sql - How to quickly find match with different string lengths -

sql - How to quickly find match with different string lengths -

i've been trying come psuedo logic figure out following.

i have table hundred one thousand records

the primary key id such as

id, col1, col2, col3 a3242x,,, b323x,,, a3242y,,, y32yxx,,,

now have user input such a3242xabc123 , y32 , ... , ...

my output figure out records in table id in table begins of input values or of input values begin values in id table.

in case output

a3242x (id a3242xabc123 begins this) , y32yxx (the input y32 begins this).

my thoughts loop thru input values, compare , subtract char end....such as

search_var=a3242xabc123; loop select id table value = search_var; if found exit; search_var = substr(search_var, 0,length(search_var-1)); end;

--basically loop thru until find value.

--for sec scenario think can somethign like

search_var[1] = a3242xabc123; search_var[2] = y32yxx; = 1 2 loop select id table id begins search_var; add together id result array; end;

i'm looking efficient way this. in cases user come in maybe few dozen id's.

forget array , set vars table. can like:

select * t cross bring together vars on t.id concat(vars.val, '%') or vars.val concat(t.id, '%')

in sql server, can do:

with vars (select 'var1' val union select 'var2' val union . . . ) select * t cross bring together vars on t.id vars.val + '%' or vars.val t.id + '%'

sql