Re: SQL query - string functions

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 15 Dec 2004 11:32:14 -0800
Message-ID: <113139134.00012542.047_at_drn.newsguy.com>


In article <c0174f26.0412151124.208c68ea_at_posting.google.com>, Knut Hovda says...
>
>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

if there is always a "trailing single column"

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

_ matches at least and at most ONE character. so, if there is always that trailing character and you just want to ignore it, _

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corportation
Received on Wed Dec 15 2004 - 20:32:14 CET

Original text of this message