SQL Server Performance Forum – Threads Archive
Comparting Text
I have a table called trackingpixels (schema and example of data below) I need to find out which adid’s have the auto pixel, but not the realestate pixel and vice versa. I’m having trouble when trying to compare the pixel as it is in text format. Any Ideas? This give me the list of ADID’s, but I can’t seem to pull in the pixelselect count(adid), adid from trackingpixels
group by adid
having count(adid) = 1 ADID (Numeric) Pixel (Text) Type (nvarchar) Subtype (nvarchar) Clientname (varchar) Here’s a few rows of data 1233 <img src="https//reporting.com/x.gif?cid=RealEstate> N L Name
1234 <img src="https//reporting.com/x.gif?cid=Auto> A L Name
1234 <img src="https//reporting.com/x.gif?cid=RealEstate> N L Name
1235 <img src="https//reporting.com/x.gif?cid=RealEstate> N L Name
1235 <img src="https//reporting.com/x.gif?cid=Auto> A L Name
1236 <img src="https//reporting.com/x.gif?cid=Auto> A L Name
figured it out.. There maybe a better way, but this is what I ended up with insert into trackingpixels select adid,'<img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=auto[IDENTIFIER]">’,’N’,’L’,’Reply’
from trackingpixels
where adid in
(select adid from trackingpixels
group by adid
having count(adid) = 1)
and pixel like ‘<img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate%’
figured it out.. There maybe a better way, but this is what I ended up with insert into trackingpixels select adid,'<img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=auto[IDENTIFIER]">’,’N’,’L’,’Reply’
from trackingpixels
where adid in
(select adid from trackingpixels
group by adid
having count(adid) = 1)
and pixel like ‘<img src="https://reporting.cpcmanager.com/ct/146/x.gif?cid=realestate%’
]]>