Tokenizing column value to rows in SQL
While I was working on SQL query optimization, I came across the following sql “where” clause.
Let me explain the query first. Query was fetching all the developers who are connected to the provided project ID. And the end-user can invoke the query by providing a single project ID or list of project ID's which are separated by 'semicolon'.
For example,
Search value : “proj001;proj201;proj301;proj302”
(of couse this value is escaped before sending it to database (Oracle) to execute).
select *
from developers d
where (instr(';' || :SEARCH || ';', ';' || d.projectID || ';')) > 0;
-- And the :SEARCH will contain "proj001;proj201;proj301;proj302"
How could I tune this query?
Well, if I could convert the search value ( semicolon separated project ID's) into a virtual table of rows then I could perform a join statement and fetch all the records.
I could do it in two ways.
Tokenizing the string value using substr and instr functions.
select *
from developers d ,
( select
substr(search_text,
decode(level, 1, 1, instr(search_text, ';', 1, level-1) +1),
instr(search_text, ';', 1, level) - decode(level, 1, 1, instr(search_text, ';', 1, level-1) +1) ) proj_id
from (select :SEARCH || ';' search_text from dual)
connect by level <= length(search_text) - length(replace(search_text, ';'))) s
where d.proj_id = s.proj_id;
Tokenizing the string using regEx.
select *
from developers d ,
( select
regexp_substr(search_text, '[^;]+', 1, level) proj_id
from (select :SEARCH || ';' search_text from dual)
connect by level <= length(search_text) - length(replace(search_text, ';'))) s
where d.proj_id = s.proj_id;