Solved

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

Posted on 2004-09-16
3
2,654 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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