SQL query - string functions

[Knut Hovda]

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

[Thomas Kyte]

In article <c0174f26.0412151124.208c68ea@posting.google.com>, Knut Hovda says...Hello,Sorry if this is not the right group, but here is a small SQL queryproblem:I have a table with a column 'name', and values could be e.g. A-1C A-10CThe first part of the name is the actual name (A-1 or A-10), and C isan optional extension that could be any number of characters.Now I want to retrieve the A-1C row, but the extension is unknown andirrelevant, 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 Ido not get the A-10C row also?I am aware of the INSTR and SUBSTR function in Oracle, but since all Iknow is that an extension will not start with a digit, they do notseem flexible enough to limit the query as needed.I am also aware that Oracle 10g has regexp_like, which probably couldhelp, 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 isit possible to do with an SQL query? Or maybe write a PL/SQLprocedure?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

[FredBear]

"Knut Hovda" <knut_hovda@hotmail.com> a écrit dans le message de
news:c0174f26.0412151124.208c68ea@posting.google.com...
| 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;

or

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');

or

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

Regards
Michel Cadot

[Daniel Morgan]

Knut Hovda wrote: 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.

SELECT keyword
FROM v_$reserved_words
WHERE keyword LIKE 'NA%';

Only have a few seconds and while this may not be your current issue
it certainly is an issue worthy of being addressed.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)