SQL Server query WHERE LIKE

Question

I currently start learning SQL query in SQL Server 2022, I want to get productId and name with name prefix 'Chain'.

I try this SQL:

SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE 'Chain%';

And I get this result:

ProductID   Name
---------------------------
   952      Chain
   324      Chain Stays
   322      Chainring
   320      Chainring Bolts
   321      Chainring Nut

But I expected:

ProductID      Name
-------------------------
   952         Chain
   324         Chain Stays

How can I achieve this?

`SELECT ProductID, Name FROM Production.Product WHERE Name = 'Chain' OR Name LIKE 'Chain %';` — Jerry Jeremiah, Aug 29 '23 at 04:05
Everything to the left of `%` has to be present in the data to match, so you would need Chain% to exclude Chainring, Like is NOT a word match function, it compares a given sequence of letters to the sequences of letters in the data — Paul Maxwell, Aug 29 '23 at 04:14

score 2 · Accepted Answer · edited Aug 29 '23 at 07:07

2

As Jerry Jeremiah mentioned; here is the solution:

SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE 'Chain %' OR Name LIKE 'Chain';

edited Aug 29 '23 at 07:07

Thom A

88,727
11
45
75

answered Aug 29 '23 at 04:19

Sam

63
5

1

The part `Name LIKE 'Chain'` should be simplified to `Name = 'Chain'` – Jonas Metzler Aug 29 '23 at 06:13

score 1 · Answer 2 · answered Aug 29 '23 at 22:32

1

Another solution is :

SELECT ProductID, Name
FROM   Production.Product
WHERE  Name + ' ' LIKE 'Chain %' ;

answered Aug 29 '23 at 22:32

SQLpro

3,994
1
6
14

SQL Server query WHERE LIKE

2 Answers2