9

I am working with SQL Server 2005.

I need to find out only those rows for which there is a special character in “Body” column. In the following scenario, the result should be only the row with TemplateID = 2. How do we write the query for this?

CREATE TABLE #Template (TemplateID INT, Body VARCHAR(100))

INSERT INTO #Template (TemplateID,Body) VALUES (1,'abcd  1234')

INSERT INTO #Template (TemplateID,Body) VALUES (2,'#^!@')

Anything other than the following is a special character for this scenario

1) Alphabtes

2) Digits

3) Space
Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
Lijo
  • 375
  • 1
  • 4
  • 17

1 Answers1

36
SELECT
    TemplateID,
    Body
FROM
    #Template
WHERE
    Body LIKE '%[^0-9a-zA-Z ]%'

The stuff between the brackets says numbers (0-9), lowercase alphas (a-z), uppercase alphas (A-Z) and the space. The "^" makes that a "NOT" one of these things. Note that this is different though than NOT LIKE '%[0-9a-zA-Z ]%'

Tom H
  • 46,766
  • 14
  • 87
  • 128
  • Hi, knowing that this is quite old answer, but could someone elaborate how's the difference between LIKE with "^", and NOT LIKE, sir? – Spacez May 08 '20 at 16:35
  • 2
    The NOT LIKE will only return rows with NO alphanumeric characters. Using the not (^) operator in the regex will return any rows that has any non-alphanumeric characters. For example, "t%1" would not be returned with NOT LIKE, but would be returned with LIKE ^. – Tom H May 09 '20 at 18:30