CODEWITHSUNDEEP
sqlsql-server
shailendra
shailendra

Reputation: 205

Sql Sever Find Partial string in another string

I have a table

studentId        Classes
1               Auto CAD,Electronics
2               Instrumentation,Chemical,IT,English
3               Computer,Math,Hindi
4               Physics,Accountancy
5               Bengali,Geography

Now I want to search using single string "Instrumentation,Geography,Physics"

it will show the following

studentId        Classes
2               Instrumentation,Chemical,IT,English
4               Physics,Accountancy
5               Bengali,Geography

because studentid 2 contains Instrumentation, studentid 4 contains Physics and studentid 5 contains Geography

How can I do it using a SELECT statement ? not like Classes like '%Instrumentation%' or like '%Geography%' or Classes like '%Physics%' because i want to use single variable to string "Instrumentation,Geography,Physics" or any combination of string separated by comma.

Upvotes: 2

Views: 5556

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can unpack your search string to a table variable and use in an exist clause.

declare @S varchar(500)
set @S = 'Instrumentation,Geography,Physics'

declare @T table(Class varchar(50))

while len(@S) > 0
begin
  insert into @T values(left(@S, charindex(',', @S+',')-1))
  set @S = stuff(@S, 1, charindex(',', @S+','), '')
end

select *
from YourTable as YT
where exists (
               select *
               from @T as T
               where ','+YT.Classes+',' like '%,'+T.Class+',%'
             )

SQL Fiddle

Upvotes: 1

Neil Thompson
Neil Thompson

Reputation: 6425

You could use the T-SQL Like statement, but ideally you would normalize your database as you are not really supposed to have multiple values in the same field - for exactly this reason.

The problem with Like is that it can potentially match unwanted terms - a Like for %physics% would also match the 'astro-physics' course - which might not be what you want.

In Joe's example - adding commas will work (in this case) but this code is brittle (being a hack really) - if the opportunity is still there my recommendation would be to normalise the database properly.

Upvotes: 2

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

SELECT * 
FROM    your_Table
WHERE   ','+Classes+',' like '%,Instrumentation,%'
OR      ','+Classes+',' like '%,Geography,%'
OR      ','+Classes+',' like '%,Physics,%'

I have added a comma with column as well as in the search criteria so that it will return only strings ending with commas

Upvotes: 2

Related Questions