November 24, 2014 at 11:54 pm
I have a table Table2 which has Id values embedded in text in a field called Detail, and I have another table Table1 with a field called ID which contains the ID values. I have the query below which can filter for records from Table2, based on a few ID values from Table1, but I would like to modify it to filter for all the ID values from Table1, something maybe like concatenating the wildcard operator on to every value in the field ID. Any help would be greatly appreciated.
Query:
select * from Table2 where Detail like
'%id=500%' or Detail like '%Id=225%' or Detail like '%Id=225%'
November 25, 2014 at 6:18 am
If the string is less than 8000 chars you could use Jeff Moden's 8K splitter like this
DelimitedSplit8K(REPLACE(REPLACEDetail,'id=',','),' ',''),',')
to get a list of ids
This depends on the variability of the contents of Detail, would need a good sample of what Detail looks like
Far away is close at hand in the images of elsewhere.
Anon.
November 25, 2014 at 6:57 am
Based on what I know thus far, I would take an approach similar to David's and use DelimitedSplit8K to split the column into its component parts and then query for them based on the ID's value. One of my goals would be to eliminate the leading % in the query and splitting out the actual value would accomplish this. Like David said, it would depend on what's in your Detail column.
I don't know if you have the option of doing so, but I would probably look at updating the design of the table to not require such strange queries to get at the rows you're after. Granted, I don't know your whole structure, but it looks a bit troublesome to me.
November 25, 2014 at 9:34 am
would you please to post the tables structure with sample data to be have a full view
November 25, 2014 at 9:47 am
In theory, the code below would do what you've described.
select t2.*
from Table2 t2
where
exists(select 1 from table1 t1 where t2.detail like '%' + t1.ID + '%')
But you also need to make sure that detail of "id=5001" doesn't accidentally match id=500. Without any more details, I'd suggest something like below to avoid that error. This is not especially efficient, but again, without more details, I can't specify a better way.
select t2.*
from Table2 t2
where
exists(select 1 from table1 t1 where t2.detail + '.' like '%' + t1.ID + '[^0-9]%')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply