use "where [field] like [a-z]" with Oracle 9i

Hi Experts,

I have a problem here with Oracle 9i. I have a sql statement using mysql but I don't know how to convert this statement to Oracle.

MySQL: select * from [table] where [column] NOT REGEXP '^[a-z]'

Which means i'm selecting all the data from [table] where the [column] first letter does not range between A to Z.
I want to display all the data in my table that [column]'s first letter is not between A to Z.
How would i be able to use this statement i Oracle?
Thanks for your advises!

Jimbo
LVL 1
jimboVBAsked:
Who is Participating?
 
andrewstConnect With a Mentor Commented:
Regular expressions are available in 10G.  But this one is easy enough without:

where SUBSTR(column,1,1) NOT BETWEEN 'a' AND 'z'

or if you want case-insensitive result:

where LOWER(SUBSTR(column,1,1)) NOT BETWEEN 'a' AND 'z'
0
 
jimboVBAuthor Commented:
Hi andrewst

Your solution LOWER(SUBSTR(column,1,1)) NOT BETWEEN 'a' AND 'z' worked for me, thanks!
But I like to know how this code works, can you give me a brief explaination before I accept your answer? Thanks a lot! XD
0
 
andrewstCommented:
SUBSTR(column,1,1) returns the substring of the string in column, starting at position 1 and with length 1.  So if the string was 'ABC' then it would return 'A'.
LOWER returns the string after changing any uppercase letters in it to lowercase.  So if the straing was 'Ab123cD!' it would return 'ab123cd!'
Finally, NOT BETWEEN 'a' AND 'z' compares the single character returned by LOWER(SUBSTR(column,1,1)) with the range 'a' to 'z' inclusive.
0
All Courses

From novice to tech pro — start learning today.