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

Question

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

May be you misspelled TRCK? Try using TRUCK..

boateng
– boateng

2018-08-28 14:37:02 +00:00
Commented Aug 28, 2018 at 14:37 — boateng
– boateng, Commented Aug 28, 2018 at 14:37

Gary_W · Accepted Answer · 2018-08-28 19:54:23Z

2

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>

edited Aug 28, 2018 at 19:54

answered Aug 28, 2018 at 19:09

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kayakpim · Accepted Answer · 2018-08-28 14:49:01Z

0

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 _

edited Aug 28, 2018 at 14:49

answered Aug 28, 2018 at 14:42

kayakpim

9932 gold badges11 silver badges28 bronze badges

5 Comments

hotfix Over a year ago

that would not work if a string is e.g. like '1/2 TON TRCK W/TOOLS x' it should only be matched with (A) and (B)

kayakpim Over a year ago

only (A) and (B) or some other things you haven't mentioned yet? If the former then the IN clause above will work fine

LifHkr Over a year ago

This worked. I assumed it was choking on the "/". As HotFix mentioned it does not capture the following characters but in my case this is acceptable.

LifHkr Over a year ago

I would prefer to use the IN command or REGEX but they do not seem to accept wildcards

William Robertson Over a year ago

But I thought you also needed it to match '3/4-1 TON TRUCK W/TOOLS'? By the way, only like accepts wildcards % and _. The regexp_ functions use regex syntax.

hotfix · Accepted Answer · 2018-08-28 14:35:57Z

0

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

answered Aug 28, 2018 at 14:35

hotfix

3,39624 silver badges36 bronze badges

1 Comment

Ram Ghadiyaram Over a year ago

pls add some explanation to the query to have better understanding : Review

LifHkr · Accepted Answer · 2018-08-28 15:47:13Z

0

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

answered Aug 28, 2018 at 15:47

LifHkr

356 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related