CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
salty
salty

Reputation: 614

Search a column for values LIKE in another column

I searched but couldn't find what I was looking for, maybe I'm not looking for the right terms though.

I have a colum for SKUs and a Keyword column, the SKUs are formatted AA 12345, and the Keywords are just long lists of words, what I need to do is find any records where the numbers in the SKU match any part of the Keywords, I'm just not sure how to do this. For example I'd like to remove the AA so that I'm looking for %12345% anywhere inside of the value of keywords, but I need to do it for every record.

I've tried a few variations of:

SELECT *, Code AS C FROM Prod WHERE Keywords LIKE '%C%';

but I get errors on all of them. Can someone help?

Thank you.

EDIT: Okay, sorry about that, the question wasn't the clearest. I'll try to clarify;

The SKU column has values that have a 2 letter prefix in front of a varying amount of numbers such as, AA 12345 or UN 98767865

The Keywords columns are full of information, but also include the SKU values, the problem here is that some of the keyword columns contain the SKU values of products that have entirely different records

I'm trying to find what columns contain the value of different records.

I hope that's more understandable.

EDIT EDIT: Here is some actual sample data

Code: AD 56409429
Keywords: 56409429, 409249, AD 56409429, AD-56409429, Advance 56409429, Nilfisk 56409429, Nilfisk Advance 56409429, spx56409429, 56409429M, 56409429G, 56409429H, ADV56409429, KNT56409429, Kent 56409429, AA 12345

Code: AA 12345
Keywords: AA 12345, 12345, Brush

I need to find all the records where an Errant Code value has found it's way into the Keywords, such as the first case above, so I need a query that would only return the first example

I'm really sorry my explanation is confusing, it's perhaps an extension of how confused I am trying to figure out how to do it. Imagine me sitting there with the site owner who added thousands of these extra sku numbers to their keywords and having them ask me to then remove them :/

Upvotes: 1

Views: 279

Answers (2)

Hogan
Hogan

Reputation: 70538

Stuff() seems better suited here.... I would do this:

SELECT * 
FROM Prod WHERE 
Keywords LIKE '%' + STUFF(SKU,1,3,'') + '%'

This will work for both AA 12345 and UN 98767865 -- it replace the first 3 characters with blank.

Upvotes: 0

connectedsoftware
connectedsoftware

Reputation: 7097

Assuming all of your SKU values are in exactly the same format you can remove the 'AA' part using SUBSTRING and then use the result in the LIKE statement:

SELECT * FROM Prod WHERE Keywords LIKE '%' + SUBSTRING(Code, 3,5) + '%'

Seeing as your SKU codes can be variable length the SUBSTRING statement above will have to changed to:

SELECT * FROM Prod WHERE Keywords LIKE '%' + SUBSTRING(Code, 3, LEN(Code)) + '%'

This will remove the first 3 characters from your SKU code regardless of the number of digits it contains afterwards.

It is not entirely clear from your question whether or not the Keywords are in the format AA 12345 or just 12345 but assuming they are and are comma separated. Then you can find all records where the code is in the keywords but there are OTHER keywords also by using this statement:

SELECT * 
 FROM Prod 
   WHERE Keywords LIKE '%' + SUBSTRING(Code, 3, LEN(Code)) + '%'
     AND Keywords <> SUBSTRING(Code, 3, LEN(Code))

This statement basically says find me all records where SKU code is somewhere in the Keywords BUT also must not exactly match the Keywords contents, i.e. there must be other keywords in the data.

Ok based on your last revisions I think this will work - or at least get you along the road (I am assuming your Product table has a primary key of Id). Also this is most likely horribly inefficient but seeing as it sounds as if this is a one off tidy up it may not matter too much as long as it works (at least that is what I am hoping).

SELECT DISTINCT P.Id
  FROM PROD P
    INNER JOIN 
    (
      -- Get all unique SKU codes from Prod table
      SELECT DISTINCT SUBSTRING(CODE, 3, LEN(CODE)) as Code FROM Prod
    ) C ON P.Keywords LIKE '%' + C.Code + '%'
    AND SUBSTRING(P.Code, 3, LEN(P.Code)) <> C.Code

The above statement joins a unique list of SKU codes (with the letter prefix removed) with every matching record via the join on the Keyword column. Note: This will result in duplicate product records being returned. Additionally the result-set is filtered so as to only return matching records where the SKU Code of the original Product record does not match a SKU code contained in the keywords column. The distinct then returns only a unique list of Product Id's that have a erroneous SKU code in the Keyword column (they have may have multiples).

Upvotes: 3

Related Questions