sql oracle compare string that contains substring

Question

I am using sql Oracle and I have in my DB a table with some values for action . For example:

id  action
1   REH001
2   REH002
3   REH003
4   LOA001
5   LOA002
6   RFL

I want to get the id if the action matches a substring of multiple values (with delimiter ;)

for example I want to compare it to the string LOA;RFL So I want to get the id if the 3 first chars are equal to LOA or RFL. For this example it has to returns the ids 4,5 and 6

But it is not always the 3 first chars. For example I want to get the ids for the match with the substring REH003;TRE For my example this will return me the id 3

I tried to simplify my question, because it is very difficult to explain, but I think you will understand with my examples.

where is the string coming from?

Vamsi Prabhala
– Vamsi Prabhala

2019-02-12 19:48:03 +00:00
Commented Feb 12, 2019 at 19:48 — Vamsi Prabhala
– Vamsi Prabhala, Commented Feb 12, 2019 at 19:48

Matthew McPeak · Accepted Answer · 2019-02-12 20:12:09Z

2

You can convert the input string to a regular expression:

where regexp_like(action, '^(' || replace(:compare_string, ';', '|') || ')')

edited Feb 12, 2019 at 20:12

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

answered Feb 12, 2019 at 19:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Matthew McPeak Over a year ago

OP said search strings are not always 3 characters

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-12 20:05:34Z

check this:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE t
    ("id" int, action varchar2(6))
;

INSERT ALL 
    INTO t ("id", action)
         VALUES (1, 'REH001')
    INTO t ("id", action)
         VALUES (2, 'REH002')
    INTO t ("id", action)
         VALUES (3, 'REH003')
    INTO t ("id", action)
         VALUES (4, 'LOA001')
    INTO t ("id", action)
         VALUES (5, 'LOA002')
    INTO t ("id", action)
         VALUES (6, 'RFL')
SELECT * FROM dual
;

Query 1:

with cte0 as 
(
   select trim(regexp_substr(c, '[^;]+', 1, levels.column_value))  as r from 
  (
     select 'LOA001;RFL' c FROM DUAL
  ) ti,
    table(cast(multiset(select level from dual 
        connect by  level <= length (regexp_replace(c, '[^;]+'))  + 1) as sys.OdciNumberList)) levels
)
select * from cte0
inner join t on substr(action, 1,length(r)) = r

Results:

|      R | id | ACTION |
|--------|----|--------|
| LOA001 |  4 | LOA001 |
|    RFL |  6 |    RFL |

Collectives™ on Stack Overflow

sql oracle compare string that contains substring

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related