Re: SQL query - string functions

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 15 Dec 2004 21:11:33 +0100
Message-ID: <41c09969$0$11583$>

"Knut Hovda" <> a écrit dans le message de
| Hello,
| Sorry if this is not the right group, but here is a small SQL query
| problem:
| 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

Try this:

select * from table where name like 'A-1%'  and length(translate(substr(name,4,1),'A1234567890','A'))!=0;


select * from table where name like 'A-1%' and substr(name,4,1) not in ('0','1','2','3','4','5','6','7','8','9');


select * from table where name like 'A-1%' and instr(substr(name,4,1),'0123456789') = 0;

Michel Cadot Received on Wed Dec 15 2004 - 21:11:33 CET

Original text of this message