Home » SQL & PL/SQL » SQL & PL/SQL » finding letters in an alphanumeric
finding letters in an alphanumeric [message #187304] Fri, 11 August 2006 14:42 Go to next message
ken4255
Messages: 1
Registered: August 2006
Junior Member
If I have a string that could contain numbers or letters, e.g. 3133X52Q0, how can I check it to verify whether or not there is at least one letter in the string? (Without having to do a find() for every letter in the alphabet?)
Thanks.
Re: finding letters in an alphanumeric [message #187306 is a reply to message #187304] Fri, 11 August 2006 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
by using TRANSLATE/REPLACE
convert all letters to "9"
IF (ORIGINAL = PROCESSED)
THEN
-- original had only numbers
ELSE
-- original had letters
END IF;
Re: finding letters in an alphanumeric [message #187308 is a reply to message #187304] Fri, 11 August 2006 15:20 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
For 10g:

SQL> select decode(regexp_instr('3133X52Q0','[[:alpha:]]'),0,'NO Letter in String','Letter in string') Status
  2  from dual;

STATUS
----------------
Letter in string


Otherwise:

SQL> select decode(length('3133X52Q0')-length(translate(lower('3133X52Q0'),'#abcdefghijklmnopqrstuvwxyz','#')),
  2     0, 'NO Letter in String', 'Letter in String') Status from dual;

STATUS
----------------
Letter in String

[Updated on: Fri, 11 August 2006 15:25]

Report message to a moderator

Previous Topic: parse a stirng
Next Topic: string converstion
Goto Forum:
  


Current Time: Sun Dec 04 14:57:11 CST 2016

Total time taken to generate the page: 0.06461 seconds