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
If we select all rows we get 3 rows which is the the same number of rows we populated but we want unique values.
--returns three rows
To get unique value we can use distinct (or group by) below is result. Noticed that we get 2 rows. DISTINCT actuall handled value with space by itself but it didn't handle TAB character so we were left with 2 rows instead of 1.
--returns two rows, it actually removed space??!!
SELECT DISTINCT Field
DISTINCT behaviour is a bit of a suprise for me as I must admit I didn't know (until now) it would remove trailing spaces (I used sql server 2008 r2). In the past I would use RTRIM like that
--returns two rows, the same as above
--with explicit removing of trailing space
SELECT DISTINCT RTRIM(Field)
So what is the solution to our problem? How can I using SQL remove special characters? In this case ASCII 9 (TAB). My solution is to replace special character with empty string. In order to replace special character I will actually use code 9 (=TAB) and CHAR function that will conver code to character. CHAR(9)
--this one replaced tab character which is ASCII 9
--using CHAR(9) function (equivalent to tab) with empty string ''
I hope this example will help you find a way to remove special characters from field values, TAB ASCII 9 is one example but this approach should work for other special characters.