SQL Server - AS [XXXX]
I am trying to return only values that contain the text [XXXX] in a specific field. My query is returning values that have 4-digit numbers, as I assume SQL Server treats [XXXX] as any 4-digit number.
I have a test query below with a list of values to demonstrate the problem.
select X.VadDesc
From (VALUES
('Product AAAA Description'),
('Product BBBB Description'),
('Product [XXXX] Description'),
('Product2 [XXXX] Description'),
('Product 2 [AAAA] Description'),
('Product X 1234 Description'),
('Product X 1235 Description'),
('Product X 1236 Description'),
('Product X 1237 Description')
) as X (VadDesc)
Where X.VadDesc like '%[XXXX]%'
My request should only return:
Product [XXXX] Description
Product2 [XXXX] Description
But it returns:
Product [XXXX] Description
Product2 [XXXX] Description
Product X 1234 Description
Product X 1235 Description
Product X 1236 Description
Product X 1237 Description
How can i do this? I don't just want to search for XXXX, as it might be on strings that I don't want to return.
+3
source to share
2 answers
select X.VadDesc
From (VALUES
('Product AAAA Description'),
('Product BBBB Description'),
('Product [XXXX] Description'),
('Product2 [XXXX] Description'),
('Product 2 [AAAA] Description'),
('Product X 1234 Description'),
('Product X 1235 Description'),
('Product X 1236 Description'),
('Product X 1237 Description')
) as X (VadDesc)
Where X.VadDesc like '%[[]XXXX]%'
+4
source to share
Use the symbol the Escape , to avoid [
like this '%\[XXXX\]%' {escape '\'}
.
Query
select X.VadDesc
From (VALUES
('Product AAAA Description'),
('Product BBBB Description'),
('Product [XXXX] Description'),
('Product2 [XXXX] Description'),
('Product 2 [AAAA] Description'),
('Product X 1234 Description'),
('Product X 1235 Description'),
('Product X 1236 Description'),
('Product X 1237 Description')
) as X (VadDesc)
Where X.VadDesc like '%\[XXXX\]%' {escape '\'}
+5
source to share