SQL query - string functions

From: Knut Hovda <knut_hovda_at_hotmail.com>
Date: 15 Dec 2004 11:24:24 -0800
Message-ID: <c0174f26.0412151124.208c68ea_at_posting.google.com>



Hello,

[Quoted] [Quoted] Sorry if this is not the right group, but here is a small SQL query problem:

[Quoted] I have a table with a column 'name', and values could be e.g.

 A-1C
 A-10C

The first part of the name is the actual name (A-1 or A-10), and C is an optional extension that could be any number of characters.

Now I want to retrieve the A-1C row, but the extension is unknown and irrelevant, so I must use a wildcard for this. If I do

  select * from table where name like 'A-1%';  

I get both of the rows above. How can I rewrite the query to ensure I do not get the A-10C row also?

I am aware of the INSTR and SUBSTR function in Oracle, but since all I know is that an extension will not start with a digit, they do not seem flexible enough to limit the query as needed.

I am also aware that Oracle 10g has regexp_like, which probably could help, but I have to choose a solution that runs with Oracle 8i and 9i.

Of course, I can always limit the selection in the code later, but is it possible to do with an SQL query? Or maybe write a PL/SQL procedure?

In advance, thanks for your help.

Regards,

Knut Received on Wed Dec 15 2004 - 20:24:24 CET

Original text of this message