In this SQL Tutorial I will show you how to using SQL remove special ascii characters. ASCII is a special code that represent a "special characters" for instance when you hit TAB on your keyword in word document it will actually saved behind the scenes as ASCII code 9 which makes sense because users want to use TAB and they don't care how it is done.
Special character may cause certain issue. For instance today once again I faced issue with special characters. I investigated existing table that stored user IDs and what I noticed is that some people would type username and hit TAB and tab would be in the table field (application should remove it but it didn't) so I had to remove this duplication for my purposes and I replaced TAB ASCII 9 with empty string. Below is code that you can follow to see this particular example in more details. Please be aware that I used SQL Server 2008 R2 and certain results might be slightly different in different vesion of SQL Server (DISTINCT to be precise).
First let's create simple a table for demostration purposes that will contain just one field:
IF OBJECT_ID ('dbo.SpecialCharacter') IS NOT NULL
DROP TABLE dbo.SpecialCharacter
CREATE TABLE dbo.SpecialCharacter (
Now let's insert 3 rows one valid values, second with trailing space (space at the end our value), and third with TAB at the end (I just hit TAB on keyboarad when I typed the value)
INSERT INTO dbo.SpecialCharacter (Field)
VALUES ('Value'), --without special characters
('Value '), --without special characters just trailing space
('Value ')-- I used TAB to generate special character
To get full access to this page
Become a member or Sign in
Membership is just $3.99/month