How to find the rows having special characters

Transact-SQL
1

Hi All,

In my table, I have more than 50K records and almost 50 Col. I need to find the records having all the special characters. I know in my table i have almost all the special characters used i.e. (ÃÂÅÄÓÈÏÖËÉÜÁÚÍÎÔ?êçêøæäöóãäåáí#). Many more special characters like these mentioned. but i am not very sure, how many more. My purpose is to delete all the rows having such special characters. These special characters are mixed with alphanumeric charaters in my records.

I also had done the manual quick analysys for few columns and i know which special characters are used in those columns, i have deleted those rows however i am still not sure if i have deleted rows having all the special symbols. If i can find the rows only containing Alphanumeric rows, i can match with the total rows if it is same to make sure i dont have any other special symbol rows in my column.

Please suggest.

Thanks
Abhishek

2 
select *
from
	yourTable
where
	col1 not like '%^[a-zA-Z0-9+-]%'
3

Thanks James for your response. I have tried this but it is giving me the list of total number of records only. i. e I have tried with CITY Column. It is giving me the records having ABCDÂEFG such city names as well where we have special characters.

4

what is the collation of your database?

5

If I understood your question correctly. My database is having details about Address, Names and employee information. Details are having special characters in almost every position of the string and records.

6

This is a side effect of the sort order for the collation used. You have two choices.

A) Explicitly list all the characters you want to allow, for example like this:

WHERE
	City LIKE '%[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789+-]%'

B) Force the collation to something that has a sort order that will work for you. For example this perhaps

WHERE
	City COLLATE SQL_Latin1_General_CP850_BIN 
              LIKE '%[^A-Za-z0-9+-]%'  COLLATE SQL_Latin1_General_CP850_BIN

If you want to be absolutely sure, use (A)

7

Hi James,

Thanks for your response. I was trying with the A option however you know it wont give me the expected outcome because my special charaters are not only in first position, they are in different different positions. Also I tried witht he (B) option however it is giving me the result of couple of symbols but not for all the symbols. i.e

It worked in finding below characters:
?ABC
ÉBCDEF
ÖFDSER
ÜCST

however below special characters are not coming in the query used.
AÄBCD
ABFGÏB
AFRDGËFG
AFRÂFG

Could you please suggest if there is any other way if such other special symbols can also come in our query.

8

I don't quite know why you are not able to isolate the special characters. In the second query that uses a collation, it could happen depending on the sort order used by the collation. But the first query should work correctly. Here is an example that I created, and it seems to work correctly. Copy this code and run it and see if that gives you the correct results.

-- Create a test table
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;
CREATE TABLE #Test(id INT, City NVARCHAR(32));

-- Rows 1-8 with special characters
INSERT INTO #Test
VALUES 
(1, '?ABC'),
(2, 'ÉBCDEF'),
(3, 'ÖFDSER'),
(4, 'ÜCST'),
(5, 'AÄBCD'),
(6, 'ABFGÏB'),
(7, 'AFRDGËFG'),
(8, 'AFRÂFG');

-- Rows 9-14 have no special characters
INSERT INTO #Test
VALUES 
(9, 'ABC'),
(10, 'abcABC'),
(11, 'abcABC123'),
(12, '+17'),
(13, '-20'),
(14, '0xFF12');


-- Pick rows that have special characters. Output should show rows 1-8
SELECT
    *
FROM
    #Test
WHERE
	City LIKE '%[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789+-]%'


-- Pick rows that do not have special characters. Output should show rows 9-14
SELECT
    *
FROM
    #Test
WHERE
	City  NOT LIKE '%[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789+-]%'
9

Hi James,

Thanks for your detailed explanation and time. This option worked for me and I am able to find special characters rows.

Thanks a lot.