Extract ASCII Characters from a string
This was a common requirement from one of the
user in the newsgroups. The user wants to extract all the charaters from a
given string and remove number or any other special characters present in the
string.
For this requirement I thought of creating a simple
function that would remove all the numbers and special characters that are
present in the given string and return the output.
------ Copy code from here --------
IF EXISTS (SELECT 1 FROM sysobjects WHERE name='ASCIICharOnly')
DROP FUNCTION dbo.ASCIICharOnly
GO
CREATE FUNCTION dbo.ASCIICharOnly (@CharData VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @Index INT,
@ASCIIData VARCHAR(50),
@ASCIIChar INT
-- Initialization code
SET @Index = 1
SET @ASCIIData = ''
WHILE @Index < LEN(@CharData)+1
BEGIN
SET @ASCIIChar = ASCII(SUBSTRING(@CharData, @Index, 1))
--Restrict the result to A-Z and a-z
IF @ASCIIChar BETWEEN 65 and 90 or @ASCIIChar BETWEEN 97 and 122
BEGIN
-- Construct the output characters
SET @ASCIIData = @ASCIIData + CHAR(@ASCIIChar)
END
SET @Index = @Index + 1
END
RETURN @ASCIIData
END
GO
-- Sample test
SELECT dbo.ASCIICharOnly ('Vinod1234Kumar!@$%Test') AS CharValue------ Stop copy of code -----------
Nice little code that does a good activity of removing
unwanted charaters from the given string. The typical ouput would be:
CharValue
--------------------------------------------------
VinodKumarTest
|