Searching Technique in SQL (Like,Contain)

Question

I want to compare and select a field from DB using Like keyword or any other technique.

My query is the following:

SELECT * FROM Test WHERE name LIKE '%xxxxxx_Ramakrishnan_zzzzz%';

but my fields only contain 'Ramakrishnan'

My Input string contain some extra character xxxxxx_Ramakrishnan_zzzzz

I want the SQL query for this. Can any one please help me?

Answer 1

You mean you want it the other way round? Like this?

Select * from Test where 'xxxxxx_Ramakrishnan_zzzzz' LIKE '%' + name + '%';

Answer 2

You can use the MySQL functions, LOCATE() precisely like,

SELECT * FROM WHERE LOCATE("Ramakrishnan",input) > 0 

Answer 3

Are the xxxxxx and zzzzz bits always 6 and 5 characters? If so, then this is doable with a bit of string cutting.

with Test (id,name) as (
select 1, 'Ramakrishnan'
union
select 2, 'Coxy'
union
select 3, 'xxxxxx_Ramakrishnan_zzzzz'
)

Select * from Test where name like '%'+SUBSTRING('xxxxxx_Ramakrishnan_zzzzz', 8, CHARINDEX('_',SUBSTRING('xxxxxx_Ramakrishnan_zzzzz',8,100))-1)+'%'

Results in:

id  name
1   Ramakrishnan
3   xxxxxx_Ramakrishnan_zzzzz

If they are variable lengths, then it will be a horrible construction of SUBSTRING,CHARINDEX, REVERSE and LEN functions.