This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I created a function which will remove all non-Ascii characters and special characters from the string of SQL Server.
You can use a below function for your existing data and as well as for new data. The solution of removing special characters or non-Ascii characters are always requirement Database Developers.
Please check the below function and one email example:
Create a function to remove Non-ASCII Chars:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
CREATE FUNCTION [dbo].[fn_RemoveNonASCIIChars] ( @Inputstring nvarchar(255) ) RETURNS nvarchar(255) AS BEGIN DECLARE @SQL nvarchar(255) SET @SQL = '' DECLARE @nchar nvarchar(1) DECLARE @position int SET @position = 1 WHILE @position <= LEN(@Inputstring) BEGIN SET @nchar = SUBSTRING(@Inputstring, @position, 1) --Unicode & ASCII are the same from 1 to 255. --Only Unicode goes beyond 255 --0 to 31 are non-printable characters IF (UNICODE(@nchar) between 192 and 198) or (UNICODE(@nchar) between 225 and 230) SET @nchar = 'a' IF (UNICODE(@nchar) between 200 and 203) or (UNICODE(@nchar) between 232 and 235) SET @nchar = 'e' IF (UNICODE(@nchar) between 204 and 207) or (UNICODE(@nchar) between 236 and 239) SET @nchar = 'i' IF (UNICODE(@nchar) between 210 and 214) or (UNICODE(@nchar) between 242 and 246) or (UNICODE(@nchar)=240) SET @nchar = 'o' IF (UNICODE(@nchar) between 217 and 220) or (UNICODE(@nchar) between 249 and 252) SET @nchar = 'u' IF (UNICODE(@nchar)=199) or (UNICODE(@nchar)=231) -- letter Ç or ç SET @nchar = 'c' IF (UNICODE(@nchar)=209) or (UNICODE(@nchar)=241) -- letter Ñ or ñ SET @nchar = 'n' IF (UNICODE(@nchar) between 45 and 46) or (UNICODE(@nchar) between 48 and 57) or (UNICODE(@nchar) between 64 and 90) or (UNICODE(@nchar) = 95) or (UNICODE(@nchar) between 97 and 122) SET @SQL = @SQL + @nchar SET @position = @position + 1 END SET @SQL = lower(@SQL) -- emails in lower case RETURN @SQL END |
Test the function:
|
1 |
SELECT dbo.fn_RemoveNonASCIIChars('anvesh^&η$%^→&*()*∇+@=gmail.com') AS ValidEmail |
Result:
|
1 2 3 |
ValidEmail ------------------ anvesh@gmail.com |
