Advertisements
Home > Data Quality, SQL, T-SQL > Find Non-ASCII character in SQL Server

Find Non-ASCII character in SQL Server

Here is a simple command to find character.

SELECT
	NOTES
	,patindex('%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_BIN,NOTES) as [Position]
	,substring(NOTES,patindex('%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_BIN,NOTES),1) as [InvalidCharacter]
	,ascii(substring(NOTES,patindex('%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_BIN,NOTES),1)) as [ASCIICode]
FROM
      tablename (NOLOCK)
WHERE
      Notes LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%'  COLLATE Latin1_General_100_BIN2

Hope it helps.

Regards,
Umesh

Advertisements
Categories: Data Quality, SQL, T-SQL Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: