I have an idea.. Try this.. a nested REPLACE together with TRANSLATE...
Select REPLACE(TRANSLATE('q###',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ-0123456789_abcdefghijklmnopqrstuvwxyz','X'),
'X', '') from DUAL;
See if that returns ###.
Maybe what's happening is if even a single character is TRANSLATE'ed to NULL
the whole string gets set to NULL.. so it doesn't matter what characters
follow that..
Joe
-----Original Message-----
From: Kemes, Lisa
Sent: Tuesday, June 14, 2011 5:57 PM Newsgroups:
public.remedy.arsystem.general
To: arslist@ARSLIST.ORG
Subject: Re: Character String Check
For some reason I get "NULL"
I would think I should get the characters ### returned...
Lisa
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza
Sent: Tuesday, June 14, 2011 5:55 PM
To: arslist@ARSLIST.ORG
Subject: Re: Character String Check
Yes it should have.. did you try it on a SQLPlus window to see what it
returns?
Select TRANSLATE('q###',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ-0123456789_abcdefghijklmnopqrstuvwxyz','') from
DUAL;
What do you get?
Joe
-----Original Message-----
From: Kemes, Lisa
Sent: Tuesday, June 14, 2011 5:37 PM Newsgroups:
public.remedy.arsystem.general
To: arslist@ARSLIST.ORG
Subject: Re: Character String Check
I tried this as well, and not sure why, but q### is returning nothing.
It should return ### correct?
Lisa
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W
Sent: Tuesday, June 14, 2011 2:58 PM
To: arslist@ARSLIST.ORG
Subject: Re: Character String Check
Something like this is where I usually take advantage of the Oracle
database's TRANSLATE function. I do a set fields (to a Display only field)
translating all letters and numbers to nothing (as well as hyphen and
underscore in your case). If there any characters left over then in a
second filter I show an error.
SQL Set Fields
Select TRANSLATE('$Field$',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ-0123456789_abcdefghijklmnopqrstuvwxyz','') from
DUAL
In pure Remedy you could do a series of replacements to remove each
character and that will do the same thing.
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Kemes, Lisa
Sent: Tuesday, June 14, 2011 1:31 PM
To: arslist@ARSLIST.ORG
Subject: Character String Check
**
I need to check a character field to make sure that the string in the field
is alphanumeric and the only special characters that are allowed are a
hyphen "-" or an underscore. No spaces allowed either.
I've tried a bunch of workflow and it's just not working for me.
Lisa Kemes
AR System Developer
TE Information Systems
Global Infrastructure and Ops
+01 717 810 2408 tel
+01 717 602 9460 mobile
lisa.ke...@te.com
MS 161-43
P.O. Box 3608
Harrisburg, PA 17105-3608
www.te.com
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"