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"
