SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Identifying records with special characters (Unicode)

Occasionally Data analyst may fall in a situation, where they need to identify records holding special characters or only the records having no special records at all. It is fairly simple to identify such records.

Let us start with creating a simple table and inserting both type of character values.

InsertNVarcharCharacter

 

Now logically, if you try to convert a NVARCHAR value to VARCHAR, SQL will fail to translate it and convert the special characters into “?”.

ConvertN_ToVarchar

We use the same logic to identify our records.

To find only the Non-Unicode Character use below query. Only the records which  are successfully converted into varchar will be able to match the original column.

SELECT *
FROM #t
Where CONVERT(varchar(20),CompanyName) = CompanyName

VarcharCharacter

 

To identify only the records with special character just replace = with <>

SELECT *
FROM #t
Where CONVERT(varchar(20),CompanyName) = CompanyName.

 

Let me know your views, questions or any other method of doing same. If you are novice and want to know more detail about VARCHAR and NVARCHAR, Drop your query in  comments i will pick that up for future.

SQLdose

I am a senior software programmer who is still working hard to get a DBA hat. I am in love with SQL Server. SQL Server for me is all about passion. I am MCSA in SQL Server. Through this blog I am giving myself an opportunity to learn more about SQL, as well as sharing my knowledge and expertise with the world.

Comments

Leave a comment on the original post [sqldose.com, opens in a new window]

Loading comments...