Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2845
  • Last Modified:

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
0
jimboVB
Asked:
jimboVB
  • 2
1 Solution
 
andrewstCommented:
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now