CODEWITHSUNDEEP
sqlt-sql
KT11
KT11

Reputation: 85

Like Predicate in T-SQL

Retrieve the product number, name, and list price of products whose product number begins 'BK-' followed by any character other than 'R’, and ends with a '-' followed by any two numerals. Question belongs to Lab file i'm working on. Below is what i'v tried:

Select p.ProductNumber, p.Name,p.ListPrice,p.ProductNumber
From SalesLT.Product as p
Where p.ProductNumber Like 'BK-%[^r]%-[0-9][0-9]'

Column Name
FR-R32B-78
FR-R32R-78
HL-U703-R
HL-U703
SO-B303-M
SO-B303-L
HL-U703-B
CA-1038
LJ-0132-S
LJ-0132-M
BK-M82S-32
BK-M82S-33
BK-M82S-38
BK-R33R-62
BK-R33R-44

Upvotes: 1

Views: 88

Answers (2)

sepupic
sepupic

Reputation: 8687

declare @t table (ProductNumber varchar(100));
insert into @t
values 

('FR-R32B-78'),
('FR-R32R-78'),
('HL-U703-R'),
('HL-U703'),
('SO-B303-M'),
('SO-B303-L'),
('HL-U703-B'),
('CA-1038'),
('LJ-0132-S'),
('LJ-0132-M'),
('BK-M82S-32'),
('BK-M82S-33'),
('BK-M82S-38'),
('BK-R33R-62'),
('BK-R33R-44')

Select *
From @t
Where ProductNumber Like 'BK-%-[0-9][0-9]' and ProductNumber not like '___R%';

Upvotes: 1

dimo raichev
dimo raichev

Reputation: 580

The problem is you have % before [^r]

Select p.ProductNumber, p.Name,p.ListPrice,p.ProductNumber
From SalesLT.Product as p
Where p.ProductNumber Like 'BK-[^r]%-[0-9][0-9]'

This should work fine i think.

The reason is %[^r]% means - Any symbols followed by not r followed by any symbols. Which is true for any of them.

Example R33R -> R is any symbols , 3 is not r and 3R is any symbols.

Upvotes: 3

Related Questions