CODEWITHSUNDEEP
regexoracle-databasespecial-characters
LifHkr
LifHkr

Reputation: 35

ORACLE Query: Select Text when value has "/" in the name

I need to select items in a table when they match the following criteria:

1/2 TON TRCK W/TOOLS (A) or 1/2 TON TRCK W/TOOLS (B)

But DOES NOT MATCH 3/4-1 TON TRUCK W/TOOLS or any other combination.

I've tried using wildcards. 

Select *
FROM Table
WHERE UNIT_ID LIKE '1/2 TON TRCK W/TOOLS (A)'

This does not work. The only thing that seems to work is using '%Tools'. Unfortunately this pulls in other records that I do not want.

I've also tried using REGEX

WHERE regexp_like (unit_id, ' ^ (1/2 TON TRCK W/TOOLS) ')

Upvotes: 1

Views: 251

Answers (4)

Gary_W
Gary_W

Reputation: 10360

At the risk of beating a dead horse, you can use regexp_like, just escape the parenthesis as they have special meaning in the regex 'language' and use the pipe symbol for an OR. This regex specifically looks for an 'A' or a 'B' character (denoted by the square brackets) inside of literal parenthesis.

SQL> with tbl(str) as (
     select '1/2 TON TRCK W/TOOLS (A)' from dual union all
     select '1/2 TON TRCK W/TOOLS (B)' from dual union all
     select '3/4-1 TON TRUCK W/TOOLS'  from dual
   )
   select str
   from tbl
   where regexp_like(str, '1/2 TON TRCK W/TOOLS \([A|B]\)');

STR
------------------------
1/2 TON TRCK W/TOOLS (A)
1/2 TON TRCK W/TOOLS (B)

SQL>

Upvotes: 2

LifHkr
LifHkr

Reputation: 35

REGEX does work if I include a wildcard for the parenthesis around the A

WHERE regexp_like (unit_id, '(1/2 TON TRCK W/TOOLS.A.)')

or 

WHERE UNIT_ID LIKE '1/2 TON TRCK W/TOOLS (%)'

as kayakpim pointed out

Upvotes: 0

kayakpim
kayakpim

Reputation: 995

You don't have a wildcard in your example though, it should be 

Select *
FROM Table
WHERE UNIT_ID LIKE '1/2 TON TRCK W/TOOLS (%)'

Like won't work without a % or _

Upvotes: 0

hotfix
hotfix

Reputation: 3396

Select *
FROM Table
WHERE UNIT_ID in('1/2 TON TRCK W/TOOLS (A)', '1/2 TON TRCK W/TOOLS (B)')

Upvotes: 0

Related Questions