Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-09-16
3
Medium Priority
?
2,781 Views
Last Modified: 2012-05-05
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
Comment
Question by:jimboVB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
andrewst earned 400 total points
ID: 12073649
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
 
LVL 1

Author Comment

by:jimboVB
ID: 12080848
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
 
LVL 15

Expert Comment

by:andrewst
ID: 12082437
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question