Sql-server – SQL Substring in WHERE clause

sql-server-2012

I'm trying to build a SQL query that will show me all the products that have products.description IS NULL but also have a corresponding description in the descriptions table. So code is always a substring of info.

The products.code is a substring of descriptions.info, so I'm not sure how to pull this.

products

id |  code   |  description
--------------------------
1    12345-1      NULL

descriptions

id |  info
---------------------------
1     12345-1_Hammer

Best Answer

You need t o compare product.info with descriptions.info. If the needed condition is for one to be a substring of the other, it can be done with LIKE.

Since there may be more than one descriptions matching (having as substring) a product code, we should use an EXISTS subquery:

SELECT p.id, p.code
FROM products AS p
WHERE p.description IS NULL
  AND EXISTS
      ( SELECT *
        FROM descriptions AS d 
        WHERE d.info LIKE '%' + p.code + '%'
      ) ;

Related Solutions

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

Following worked for me on SQL Server 2008:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master_key_password'
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY

For me it was essential to drop the encryption first, although the 'add' command should usually do this automatically.

SQL Server – Finding Rows Where Data Resets

;WITH x AS 
(
  SELECT ID, VAL, Prev_VAL = LAG(VAL, 1) OVER (ORDER BY ID) 
  FROM dbo.your_table
)
SELECT ID, VAL, Prev_VAL, Cumulative = VAL + Prev_VAL
FROM x 
WHERE VAL < Prev_VAL
ORDER BY ID;

If you need to support versions older than SQL Server 2012 (when LAG() was introduced), you can do this, but by casual and hardly-scientific observation, it is about 4X as expensive:

;WITH x AS 
(
  SELECT ID, VAL, Prev_ID = ROW_NUMBER() OVER (ORDER BY ID)
  FROM dbo.your_table
)
SELECT c.ID, c.VAL, p.VAL, Cumulative = c.VAL + p.VAL
FROM x AS c
LEFT OUTER JOIN x AS p
ON c.Prev_ID = p.Prev_ID + 1
WHERE c.VAL < p.VAL
ORDER BY c.ID;

Best Answer

Related Solutions

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

SQL Server – Finding Rows Where Data Resets

Related Question