Solved

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

Posted on 2004-09-16
3
2,600 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
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
andrewst earned 100 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MULTIPLE DATE QUERY 15 91
constraint check 2 48
Component is listed with a Protocol more than once 3 42
Oracle - SQL Query with Function 3 54
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

821 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