CODEWITHSUNDEEP
sqloracle-database
Jérémie
Jérémie

Reputation: 13

Split string based on a table of values

I am trying to write an SQL query in which I need to split string value based on a list of values existing in a reference table.

For example I have the string value "JOHN DOE SILVER" and the value "JOHN" is present in the reference table, I want to get "JOHN" as a result.

In the reference table, there is only one column with all values that can be found in the string. The value is always at the beginning of the string. I can have spaces in the value, for example :

String = "EXAMPLE STRING TEST"

Value in reference table = "EXAMPLE STRING"

Result should be = "EXAMPLE STRING"

I'm not sure how I can do that... I think I first need to split the string based on "if the value at its begining is present in the reference table"... but I don't know how.

I hope it's clear enough so that somebody can help me on that !

Edit :

as it appears not to be clear enough, I try to specify.

In fact I need to populate a column in my_table with a value from the reference_table when it is found at the begnining of the 'test string' of my_table. See http://sqlfiddle.com/#!4/cbd72/1 for an attempt to test your answers

Notes :

Upvotes: 1

Views: 131

Answers (5)

Rajat
Rajat

Reputation: 5803

UPDATED

You could achieve this using window functions

select distinct 
          t.*
         ,max(r.typeref) over (partition by t.nom order by length(r.typeref) desc) as typeref
from my_table t
left join reference_table r on instr(t.nom,r.typeref)=1;

DEMO

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

The first problem I see is that you want 'Ruth' to match 'Ruth Miller', but not 'Ruthless John Brown'. I.e. the search string must either end the string or be followed by a space.

The second problem can be multiple matches, like 'John Bob Miller' found via 'John Bob' and 'John'. It suffices to show one match, i.e. the better (longer) string. Such ranking can be done with ROW_NUMBER.

With LIKE:

select *
from
(
  select
    t.*, r.typeref,
    row_number() over (partition by t.nom order by length(r.typeref) desc) as rn
  from test t
  left join reference_table r on t.nom = r.typeref or t.nom like r.typeref || ' %'
)
where rn = 1;

Demo: http://sqlfiddle.com/#!4/cbd72/52

With REGEXP_LIKE instead:

select *
from
(
  select
    t.*, r.typeref,
    row_number() over (partition by t.nom order by length(r.typeref) desc) as rn
  from test t
  left join reference_table r on regexp_like(t.nom, '^' || r.typeref || '[ $]')
)
where rn = 1;

Demo: http://sqlfiddle.com/#!4/cbd72/51

Upvotes: 2

Popeye
Popeye

Reputation: 35930

You can use INSTR function in join as following:

Select r.str
  From your_table t
  Join ref_table r
    On INSTR(t.your_column,r.str) = 1

Cheers!!

Upvotes: 3

kfinity
kfinity

Reputation: 9091

Here's how you could do it in a join.

-- sample data
with my_table as (select 'EXAMPLE STRING TEST' as str_value from dual union select 'JOHN DOE SILVER' from dual),
     reference_table as (select 'EXAMPLE STRING' as str_value from dual union select 'JOHN' from dual)
-- query
select ref.str_value
from my_table m
join reference_table ref
  on m.str_value like ref.str_value || '%'

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

You may try below -

SELECT *
FROM REFERENCED_TABLE
WHERE 'EXAMPLE STRING' LIKE '%' || COLUMN_VALUES || '%'

Upvotes: 1

Related Questions