Search Comma Delimited string in Column

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)

Search Comma Delimited string in Column

Author

Topic

cykophysh
Starting Member

24 Posts

Posted - 2007-08-06 : 10:12:32

I have a column in a table in the DB called Branch, that actually contains a comma delimited string, which is the areas the Branch services.

I need to create a query that will extract branches Email depending on the area is services. I thought I might be able to use PatIndex to search the Comma Delimited string for the area

This is the Code from my Proc

ALTER proc [dbo].[sfspGetBranchEmailByLocality]
           @locid int
as

DECLARE @CommaString VARCHAR(max)
declare @Locality varchar(max)
-- Get the Locality name 
set @Locality = (SELECT     Locality FROM Locality WHERE     (ID = @locid))
-- Append commas to the locality name
SET @CommaString = ', ' + @Locality + ',' 

SELECT     BranchEmail
FROM         Branch

where PATINDEX(',' + cast(LocalitiesServed as varchar(max)) + ',', @Commastring) > 0

However my idea doesn't seem to work , was wondering if you guys would have some pointers

Kind Regards,
Gary

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-06 : 10:23:10

Try

where PATINDEX('%'+@Commastring+'%',',' + cast(LocalitiesServed as varchar(max)) + ',') > 0

Madhivanan

Failing to plan is Planning to fail

cykophysh
Starting Member

24 Posts

Posted - 2007-08-06 : 10:35:42

Thanks for that
Tried that already and it doesn't seem to work either.

Kind Regards,
Gary

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-06 : 10:53:04

Can you fix your database design? A poor design is making what should be a simple SELECT something much slower, less efficient, and more complicated.

- Jeff
http://weblogs.sqlteam.com/JeffS

cykophysh
Starting Member

24 Posts

Posted - 2007-08-06 : 11:16:00

quote:
Originally posted by jsmith8858

Can you fix your database design? A poor design is making what should be a simple SELECT something much slower, less efficient, and more complicated.

- Jeff
http://weblogs.sqlteam.com/JeffS

This is an Inherited DB design
Sometimes you got to work with what you've got
Clients aren't always happy to engage in more work.
There is an import routine that inserts data into the DB, so changing any DB structure would potentially break the imports

So No, changing the DB design is not an option. Would love to work in the Utopia where clients are happy to pay for extensive work :-)

Kind Regards,
Gary

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-06 : 11:45:53

quote:
Originally posted by cykophysh

Thanks for that
Tried that already and it doesn't seem to work either.

Kind Regards,
Gary

this approach should work fine.
can you show us the query? you probably have extra spaces that get
in the way.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

cykophysh
Starting Member

24 Posts

Posted - 2007-08-06 : 12:00:52

quote:
Originally posted by spirit1

quote:
Originally posted by cykophysh

Thanks for that
Tried that already and it doesn't seem to work either.

Kind Regards,
Gary

I've been playing around with this

declare @Locality varchar(max)
DECLARE @SafeInClause VARCHAR(100)
Declare @LocalitiesServed varchar(max)

set @Locality = 'Area1'
set @LocalitiesServed = 'Area1, Area2, Area3, Area4, Area5'

Select @Locality
SET @SafeInClause =', ' + @Locality +', '
Select @SafeInClause
SELECT BranchEmail
FROM Branch

where
PATINDEX( @SafeInClause, ', ' + cast( LocalitiesServed as varchar(max)) + ', ' ) > 0

this approach should work fine.
can you show us the query? you probably have extra spaces that get
in the way.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kind Regards,
Gary

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-06 : 12:07:53

use either:
WHERE charindex( @SafeInClause, ', ' + cast( @LocalitiesServed as varchar(max)) + ', ' ) > 0

or
WHERE patindex( '%' + @SafeInClause + '%', ', ' + cast( @LocalitiesServed as varchar(max)) + ', ' ) > 0

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

cykophysh
Starting Member

24 Posts

Posted - 2007-08-06 : 12:13:24

Thanks Spirit1

Thats done the trick!!!

quote:
Originally posted by spirit1

use either:
WHERE charindex( @SafeInClause, ', ' + cast( @LocalitiesServed as varchar(max)) + ', ' ) > 0

or
WHERE patindex( '%' + @SafeInClause + '%', ', ' + cast( @LocalitiesServed as varchar(max)) + ', ' ) > 0

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kind Regards,
Gary

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-07 : 02:22:20

<<
PATINDEX( @SafeInClause, ', ' + cast( LocalitiesServed as varchar(max)) + ', ' ) > 0
>>

It means that you didnt look at the query I posted clearly

Madhivanan

Failing to plan is Planning to fail

cykophysh
Starting Member

24 Posts

Posted - 2007-08-07 : 04:14:08

It means I needed the '%' in the query

quote:
Originally posted by madhivanan

<<
PATINDEX( @SafeInClause, ', ' + cast( LocalitiesServed as varchar(max)) + ', ' ) > 0
>>

It means that you didnt look at the query I posted clearly

Madhivanan

Failing to plan is Planning to fail

Kind Regards,
Gary

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources