Compare exact string in oracle sql

Question

I have column in which different values placed with ':' colon separate. I want to match exact string in column. lets assume table is temp and column name is sample_col. order of values can also change.

sr_no|sample_col

1 |    'Sample-ABC'
2 |   'Sample-ABC-123'
3 |    'Sample-ABC-123:XYZ'
4 |    'Sample-ABC-123:Sample-ABC'
5 |    'XYZ:Sample-ABC'

So

1) require a query to search a records which is having value 'Sample-ABC' then 3 records(i.e sr_no 1,4,5) should come through query

2) require a query to search a records which is having value 'Sample-ABC-123' then 3 records(i.e sr_no 2,3,4) should fetch.

3) require solution for those records which are having 'Sample-ABC-123' values but not having 'Sample-ABC' then 2 records(i.e sr_no 2,3) should fetch.

I tried to get output with regexp_like,instr,like etc option but failed. can anyone help me to sorting this.

below is sql query i tried fetch records which are having 'Sample-ABC' records:

select * from temp
where instr(sample_col,'Sample-ABC') > 1
and sample_col not like '%Sample-ABC-123%'; --this exclude 'Sample-ABC-123:Sample-ABC' record from

select * from temp
where sample_col not like '%Sample-ABC%'; --this will include 'Sample-ABC-123' records as well.


select * from temp
where regexp_like(sample_col,'Sample-ABC'); --this will include 'Sample-ABC-123' records as well.

I require query which will provide output with all the

Gordon Linoff · Accepted Answer · 2019-09-18 14:37:14Z

1

You probably already know that this is a bad data format and you should be using a more appropriate structure to store multiple values, such as a table, nested table, JSON, XML or something else.

That said, you can do what you want by adding delimiters for the comparison:

where ':' || sample_col || ':' like '%:Sample-ABC:%'

This will find exact matches.

edited Sep 18, 2019 at 14:37

answered Sep 18, 2019 at 14:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

harshkumar satapara Over a year ago

Agreed with this solution but the values been inserted through front-end and its storing colon separated so if searching string inserted in last then it wont have colon.

Gordon Linoff Over a year ago

@harshkumarsatapara . . . I don't understand our comment. This does what you want.

harshkumar satapara Over a year ago

I mean to say is, this will work for 1 & 2 but not for 3rd

Gordon Linoff Over a year ago

@harshkumarsatapara . . . Of course not. You would need two comparisons for that purpose. But the idea is exactly the same. One would look for ' 'Sample-ABC-123' and one for 'Sample-ABC-123'.

Collectives™ on Stack Overflow

Compare exact string in oracle sql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related