Split and check condition in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Split and check condition in query

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-07-30 : 13:55:02

Hi

I need the solution for the below.

In my database table I have one field AbleToCover. This field contain multiple values for comma separated. I will select a some fields from that table based on AbleToCover.

Example.

Table name: Test, Fields: Type, AbleToCover

Example records

Type AbleToCover
TypeEx1 Plumbing
TypeEx2 Plumbing,Gardening
TypeEx3 Plumbing,Gardening,Electrics
TypeEx4 Plumbing

My requirement is select a Type from Test table where AbleToCover is Plumbing.

Result is

TypeEx1
TypeEx2
TypeEx3
TypeEx4

Because of Plumbing is in all the types.

Please help me.

Kamal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:59:15

[code]SELECT Type
FROM Test
WHERE ','+AbleToCover + ',' LIKE '%,Plumbing,%'[/code]

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-30 : 14:01:03

select * from table where field LIKE '%Plumbing%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 14:15:40

quote:
Originally posted by rohitkumar

select * from table where field LIKE '%Plumbing%'

it wont work correctly in all cases

consider the case where you've similar word like

TypeEx5 Plumbing & Gardening,Carpentry

then this will also be returned when you use above query. Thats why i wrapped it within ',' to make sure we take only the same words appearing among the values

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-07-30 : 14:46:26

Thanks for your valuable help. Now it is enough for my requirement. Because of I will stored the AbleToCover field in comma seperated only.

kamal,.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources