Sql Sever Find Partial string in another string

Question

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.

Joe G Joseph · Accepted Answer · 2012-09-12 08:53:27Z

2

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

edited Sep 12, 2012 at 8:53

answered Sep 12, 2012 at 8:46

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Neil Thompson · Accepted Answer · 2012-09-12 08:57:41Z

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.

Mikael Eriksson · Accepted Answer · 2012-09-12 09:17:58Z

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

Collectives™ on Stack Overflow

Sql Sever Find Partial string in another string

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related