Hyphen Special Character in Like Operator

The requirement is need to identify all the table records which have special characters except Alphabets, Numbers, Ampersand, Single Quote, Hyphen, Dot and Space in the employee_name field.

The Valid characters are:

A-Z     a-z        0-9         &           ‘         -             .     And Space

We had written a sample query using Like operator as shown below, because we are using Sql Server 2000

This is the sample query later on we will run this query against employee_name column.

Select 1 as result where 'Hi@'

LIKE   '%[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ&0-9''-. ]%'

result     

-----------

1

(1 row(s) affected)

Yes this query identified the special character “@”

Select 1 as result where 'Hi%' LIKE '%[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ&0-9''-. ]%'

result     

-----------

1

(1 row(s) affected)

Yes this query identified the special character “%”

Select 1 as result where 'Hi(' LIKE '%[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ&0-9''-. ]%'

result     

-----------

(0 row(s) affected)

Now the query failed to identify the Special character “(“  Open Bracket

The reason to fail this query to identify the special character is if we look at the LIKE pattern we have like ''-.

We include these characters because these are valid characters individually, but if we place these characters in this sequence compiler treated these characters as The range of characters between Single Quote and Dot.

Select Ascii(''''), ascii('-'), ascii('.')

                                                                             

SQ         Hyphen      dot                             

----------- ----------- -----------

39          45          46

(1 row(s) affected)

Character	ALT Code
'	Alt 39
(	Alt 40
)	Alt 41
*	Alt 42
+	Alt 43
,	Alt 44
-	Alt 45
.	Alt 46

The above LIKE pattern considered all the above characters between Alt 39 and Alt46 including boundaries

as a valid characters.

Solution: To specify the Special character hyphen as a general character we should use that character immediately after ^ Carat Symbol.

The modified Sql statement is:

Select 1 as result where 'Hi(' LIKE '%[^-abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ.&0-9 '']%'

result     

-----------

1

(1 row(s) affected)

Select 1 as result where 'Hi)' LIKE '%[^-abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ.&0-9 '']%'

result     

-----------

1

(1 row(s) affected)

Thanks…