Home             About Us             Sign Guest Book             Contact Us             Subscribe              RSS  
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar

Click on the image to subscribe to our Monthly Newsletter.


Click here for Archives

No Chats Available

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

Comment about this article
Free Hit Counters
Free Hit Counters