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.
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
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
15 People are following this question.
