You could shift this to use the regexp_instr function to identify the presence of characters.
On Thu, Aug 23, 2012 at 2:00 AM, Axton <[email protected]> wrote: > I found this works reliably on Oracle. It removes leading and trailing > whitespace characters (tabs, spaces, CR, LF, FF, vertical tab, etc.). Just > use a SQL set fields and set the field into itself: > > select (regexp_replace(regexp_replace('$your field$', '(*[[:space:]]$)', > '', 1, 0), '(^[[:space:]]*)', '', 1, 0)) from dual > > It doesn't return an error, but instead strips the leading and trailing > whitespace characters. You can set the filter up with a run if/execute > option so that it only fires when it needs to: > > Execute on: Submit, Modify > Run If Qual: 'your field' != 'DB.your field' > > > Oracle defines the [:space:] as all posix whitespace characteres and > includes the following posix characters: > > unicode: [\p{Z}\t\r\n\v\f] > ascii: [ \t\r\n\v\f] > > > see http://www.regular-expressions.info/posixbrackets.html for an > overview of Posix character classes, include :space:. > > Axton Grams > > > On Thu, Aug 23, 2012 at 12:27 AM, Misi Mladoniczky <[email protected]> wrote: > >> Hi, >> >> There is some problems with that approach... >> >> 1. It is hard to control if you get a NL or CR+NL in the field. A CR+NL >> will not match a NL-only... >> >> 2. Some database adds a space to the beginning of the string if it only >> has a CR+NL or NL in it, which would then result in SPACE+CR+NL or >> SPACE+NL... >> >> 3. To fix this, you can do a RIGHT(" >> ", 1), but you can not use functions in Run-if, and you will have to >> resort to two FLTR:s and a tmp-field anyway... >> >> Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) >> >> Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): >> * RRR|License - Not enough Remedy licenses? Save money by optimizing. >> * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. >> Find these products, and many free tools and utilities, at http://rrr.se. >> >> > Susan, >> > >> > Try this old-school trick: >> > >> > >> > >> > 1. Open a text editor like 'notepad' >> > >> > 2. In a new doc, type this: >> > >> > a. <Double-quote> >> > >> > b. <Enter> >> > >> > c. <Double-quote> >> > >> > 3. Select all and copy to an active link with a Set Field that >> puts >> > this string into a tmp_field. Use the tmp_field name in work flow. >> > >> > >> > >> > BTW, you could use the REPLACE function to change them into something >> > else, >> > like an * >> > >> > >> > >> > HTH, >> > >> > Joel >> > >> > Joel Sender [email protected] 310.829.5552 >> > >> > >> > >> > From: Action Request System discussion list(ARSList) >> > [mailto:[email protected]] On Behalf Of Susan Palmer >> > Sent: Wednesday, August 22, 2012 3:01 PM >> > To: [email protected] >> > Subject: Data validation in character fields >> > >> > >> > >> > ** >> > >> > Hi everyone, >> > >> > >> > >> > On a regular form with a character field I need to be able to validate >> > that >> > there are no 'carriage returns' at the end of the data. >> > >> > >> > >> > Example: >> > >> > The field should contain - ABC Company >> > >> > >> > >> > but instead it contains - >> > >> > ABC Company >> > >> > ABC Company >> > >> > ABC Company >> > >> > >> > >> > This is purely a human error during input. They may be copy/pasting >> from >> > a >> > spreadsheet and somehow do paste more than once or any other creative >> way >> > of >> > doing this. Unlikely they are actually typing this. The field length >> is >> > 100 characters, 76 of which display. But since only 1 row is displayed >> > it's >> > not evident when you look at the record that the above situation occurs. >> > >> > >> > >> > So I want to put a filter in that will produce an error when there is a >> > 'carriage return' (or whatever they are called these days) upon save. >> But >> > I >> > cannot figure out how to create that special character in the >> > qualification >> > line. I was thinking something like 'Site Name' LIKE "%symbol" but I >> > don't >> > know what to put where the word symbol is. There is never a situation >> > when >> > we want the CR. >> > >> > >> > >> >>From a Remedy perspective this whole thing doesn't even matter. But we >> >> have >> > other systems that suck the data out of Remedy and they break when they >> > get >> > this data. No suitable comment available for that issue. >> > >> > >> > >> > I'd appreciate any ideas you may have. >> > >> > >> > >> > Thanks, >> > >> > Susan >> > >> > >> > >> > Susan Palmer >> > >> > ShopperTrak >> > >> > 200 W Monroe 11th Floor >> > >> > Chicago, IL 60606 >> > >> > 312-529-5325 >> > >> > [email protected] >> > >> > >> > >> > ARS v7.5 >> > >> > Oracle 10g >> > >> > User Tool v7.5 >> > >> > Sun Solaris >> > >> > >> > >> > >> > >> > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> > >> > >> > >> _______________________________________________________________________________ >> > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" >> > >> >> >> _______________________________________________________________________________ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" >> > > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
