CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Ramakrishnan
Ramakrishnan

Reputation: 5436

Searching Technique in SQL (Like,Contain)

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?

Upvotes: 1

Views: 487

Answers (3)

Coxy
Coxy

Reputation: 8928

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.

Upvotes: 0

Shubham
Shubham

Reputation: 22317

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

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

Upvotes: 0

Robin Day
Robin Day

Reputation: 102478

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

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

Upvotes: 2

Related Questions