CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldatabase-administration
vidyadhar
vidyadhar

Reputation: 3168

How to use regular expression to find special charcters

How can I use regular expressions to find the rows which contains below special characters inside the name column?

 * , [ ] { } ' " \ % $ @ ( ) < > ? : ; # ! & / | = + - _ ~

Along with that we need to identify the rows which contains \n (line feed) and \r (carriage return) inside name column.

Example: I can find the rows which contains ' in name using below query.

select * from table where name like '%''%';

In the same way a single query to find all the rows which contains any of the above mentioned special characters.

Upvotes: 0

Views: 97

Answers (1)

wiretext
wiretext

Reputation: 3342

i hope this will help you 

declare @name nvarchar(33);
-- you can check with this name also
set @name  = ' yourname''g'
-- OR (here i am overriding value but please make sure at the time one SET statement should active)
set @name  = ' * , [ ] { }  " \ % $ @ ( ) < > ? : ; # ! & / | = + - _ ~ '
IF (@name LIKE '%[^a-zA-Z0-9]%')
    PRINT 'Contains "special" characters'
ELSE
    PRINT 'Does not contain "special" characters'

Upvotes: 1

Related Questions