Filter data with second character non-numeric 1

[sonny1974]

i have this in my where statement. i only want to see lot_numbers that start with R or W but i want to be sure that the next character after the R or W is a number

so if its W34322 i want it
if its WQ2321 i dont want it

where (wod.lot_nbr like 'R%' or wod.lot_nbr like 'W%')

thanks

 

[SantaMufasa]

As I posted yesterday, Sonny, this code does what you want:

...WHERE substr(wod.lot_nbr,1,1) in ('R','W')
     AND '0123456789' like '%'||substr(wod.lot_nbr,2,1)||'%'
...

The first clause confirms that position 1 is either an 'R' or a 'W';
The second clause confirms that the second position is numeric.

Let us know if you have additional questions or if this code somehow does not meet work for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

One way of doing this is

where substr(wod.lot_nbr,1,1) in ('R','W')
and substr(wod.lot_nbr,2,1) in ('0','1','2','3','4','5','6','7','8','9')

The difference between this approach and SantaMufasa's is that his where clause will match the single characters "R" and "W", whereas mine will not.

 

[Beilstwh]

and a third way is

where substr(wod.lot_nbr,1,1) in ('R','W')
and nvl(substr(wod.lot_nbr,2,1),'*') between '0' and '9'

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Cool, Bill...I like your method best of all. Hava

.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]