CODEWITHSUNDEEP
sql-serversql-server-2008-r2sql-like
UnDiUdin
UnDiUdin

Reputation: 15384

How to search for strings containing square brackets and a specific number of chars with the LIKE operator

I would like to tell to the LIKE operator in a SELECT query in SQL Server2008R2 to search for strings containing strings like these

[can]
[eur]
[usd]

basically 

SELECT DESCRIPTION
FROM TABLE
WHERE 
  DESCRIPTION contains two square brakets and there are three chars in the square brackets

Somehow these would be returned results

Freddie Young [FRY] 
Micheal Norton [MIN]

but those not

Laura Cunningham [LC]
Marc Lizard (MAL)

i imagine RegEx wih CLR is an option, but i would like to peform this with a simple query.

Upvotes: 3

Views: 187

Answers (1)

DB101
DB101

Reputation: 633

Here you go. Note the use of the ESCAPE clause to escape the [ and ] characters.

WITH cte(value)
as
(
 SELECT 'Freddie Young [FRY]' 
  Union
 SELECT 'Micheal Norton [MIN]'
  Union
 SELECT 'Laura Cunningham [LC]'
  Union
 SELECT 'Marc Lizard (MAL)')
 Select * 
   FROM cte
  Where value like '%![[A-Z][A-Z][A-Z]!]%' escape '!'

A version without escaping is below. To find an opening square bracket, you need to enclose it in square brackets. To find a closing square bracket, just use the character. No enclosing is required.

WITH cte(value)
AS
(
 SELECT 'Freddie Young [FRY]' 
  UNION
 SELECT 'Micheal Norton [MIN]'
  UNION
 SELECT 'Laura Cunningham [LC]'
  UNION
 SELECT 'Marc Lizard (MAL)'
 )
 SELECT * 
 FROM cte
 WHERE value like '%[[][A-Z][A-Z][A-Z]]%'

MSDN Reference

Upvotes: 3

Related Questions