question

kcbtke252 avatar image
kcbtke252 asked

search fields that contain commas

I'm searching a field in a table that contains commas. The field contains sales ids and it it's stores in this format: 267,549,496. This web app was created a long time ago and I've been tasked with maintaining it, lucky me. What i'd like to do is normalize the whole thing but that would be more work than I'd like to put into it so where's the thing. When someone enters a new sales ID I have to make sure it doesn't already exist before I insert it. I have a udf that will split on a character but I can't do a join and pass the Sales ID field into my function and return a table, or if someone can tell me how I'd appreciate that. Can someone give me any idea on how I can do this? I'd like to do this in a stored procedure where I accept the SID parameter, if it is comma separated i'd obviously have to split that but I still have the dilemma of the data in the table. If anyone has a solution for me I'd greatly appreciate it... Thank you.
stored-proceduresudfstring-splittingcomma-delimited
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mjharper avatar image
mjharper answered
If you're using SQL2005 you can use CROSS APPLY to join your function to your table. For example if the function that splits the string is called dbo.uf_SplitStringOnComma and returns a field called "Number" and your table is called "YourTable" and the sales Ids are stored in a column called "SalesId" then you can use the following: SELECT a.SalesId FROM YourTable a CROSS APPLY dbo.uf_SplitStringOnComma(SalesId) b WHERE b.Number = @NumberToCheck
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you... I'll give it a try.
0 Likes 0 ·
Valentino Vranken avatar image
Valentino Vranken answered
Perhaps something like this? create table #t(IDList varchar(100)) insert into #t select '12,456,78' union all select '789,56,321' union all select '23,45,78' declare @SID varchar(10) = '78'; select * from #t where ',' + IDList + ',' like '%,' + @SID + ',%' drop table #t
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.