SQL criteria based on delimited string 2

[jlitondo]

Is there a way to run a sql select query based on criteria that might be in a pipe delimited string in a table field? For example if a record has its column value as AB||CD||EF||GH, how would i run the query and select all records where this column has either the value "CD" or "GH"?

I can easily do this in vb.net/asp.net using a split function but due to the extremely large number of records (100s of thousands), I would rather have this processing run on the server in a stored procedure as opposed to running it on the client.

Thanks.

 

[gmmastros]

I strongly encourage you to normalize this data (if that's possible).

Here's one way to accomplish this.

Declare @Temp Table(Data VarChar(8000))

Insert Into @Temp Values('AB||CD||EF||GH')
Insert Into @Temp Values('XY||CD||EF||GH')

Select * 
From   @Temp 
Where  ('||' + Data + '||' Like '%||AB||%'
       Or '||' + Data + '||' Like '%||GH||%')

Please understand that I suggested normalizing your date because then you could have a nice index which would speed up this query ALOT. Do not expect this to run very fast.

 

[r937]

WHERE '||'+daColumn+'||' LIKE '%||CD||%'
   OR '||'+daColumn+'||' LIKE '%||GH||%'

will be slow as cold glue

any chance you can normalize the data? multiple values in a single column is generally a bad idea

r937.com | rudy.ca

 

[gmmastros]

slow as cold glue

Yeah. That's what I meant to say.

 

[jlitondo]

Thanks gmmastros and r937k for your quick responses. I did a test run of your recommendations and yes it is pretty slow. This is a database that i inherited so i had no control over how the data structure was established. However, i will take the time to normalize this data.

Once again, thanks.

 

[gmmastros]

Normalizing this data won't be fun, but it'll be worth it in the long run.

>> I can easily do this in vb.net/asp.net using a split function

There is not a split function built in to SQL Server, but there are handy functions around that mimic this behavior. I did not suggest this before because it requires looping in SQL Server to accomplish, which would be even slower.

However, since you want to normalize this data, you may find a split function handy. You can find several in the FAQ sections. In my opinion, the easiest to implement is:

faq183-5207

Once you have your child table built, you can use the split function in that FAQ to loop through your parent table to insert the data in to the child table.

If you run in to any difficulty doing this, feel free to let us know, and we'll see what we can do to help.