SQL Server T-SQL User-Defined Function of the Week

Remove Unwanted Characters

Volume 1 Number 50         August, 26, 2003

Check out the UDF Frequently Asked Questions at: http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF, send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at: http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Excitement is building around Novick Software. My book, Transact-SQL User-Defined Functions, is at the printer. I'm expecting the first copies in a week or so. Although they've got the title incorrect and the wrong cover, Amazon already taking orders:

But for a few dollars more, I'll be selling it bundled with an extra library of 100 UDFs that don't appear in the book and won't be published here. Stay tuned, I should have the details ready next week.

Now on to this week's UDF.....

Sometimes there are certain characters that you want to remove from a string. This week's UDF of the week does just that. It's a contribution by Vinod Kumar. The function ASCIICharOnly removes any character that isn't a letter. Both capitol or lower case letters are retained. Numbers and special characters are discarded.

Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON --
SET ANSI_NULLS ON --
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

GRANT SELECT on dbo.ASCIICharOnly to PUBLIC
GO

There are other ways to do such a comparison in SQL Server 2000 and in SQL Server 7. In SQL Server 7, one way to do a case sensitive comparison is to CONVERT both sides of the comparison operator to BINARY. That usually works, unless one expression happens to be Unicode and the other one isn't. SQL Server 2000 added the COLLATE clause that allows you to specify the collating sequence for each comparison. In the next few month's I'll be devoting a few issues to these techniques. Let's move on to try out the ASCIICharOnly function. Here's a script that shows what it does.

-- Try ASCIICharOnly 
SELECT dbo.ASCIICharOnly ('K$i3l_r&40o1y') as [Mixed]
     , dbo.ASCIICharOnly ('-=w/1A$s#$''') as [Special]
     , dbo.ASCIICharOnly ('©H€e®r®ËËËe') as [Symbols]
GO

Mixed     Special    Symbols    
--------- ---------- -----------
Kilroy    wAs        Here

(End of Results)

Thanks for the contribution Vinod.

       _____ 
      ( . . ) 
----O----0---O-----
         0

Thanks,
Andrew Novick