How to Take Out Alpha Characters But Leave Numeric Characters in SQL

Numeric data, with no alpha characters

Microsoft SQL's Transact-SQL has the ability to take out alpha characters but leave numeric characters in a string; it just requires the creation of a user-defined function. You may want to do this in cases where there is a numeric field in the database and you want to protect the integrity of your insert statement by cleaning up the data before attempting the insert. In most cases, the entire insert statement will fail if there is an alpha character in a numeric field.

Difficulty:

Easy

Instructions

1. In Microsoft SQL, create a user-defined function like:

   CREATE FUNCTION dbo.UDF_NumericOnlyChars

   (

   @string VARCHAR(8000)

   )

   RETURNS VARCHAR(8000)

   AS

   BEGIN

   DECLARE @IncorrectCharLoc SMALLINT

   SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)

   WHILE @IncorrectCharLoc > 0

   BEGIN

   SET @Numericstring = STUFF(@Numericstring, @IncorrectCharLoc, 1, '')

   SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @Numericstring)

   END

   If (@Numericstring ='')

   SET @Numericstring = '0' -- This will insure that a string of numbers is returned

   RETURN @Numericstring

   END

   GO

   This is based off of the user-defined function created by Pinal Dave.

2. Use the function like this:

   Select [dbo].UDF_NumericOnlyChars('sadDs132#dds@19')

   Which will return:

   13219

3. Use the function on a table column to only return the numeric digits in the string:

   Select FirstName, LastName, Phone, UDF_NumericOnlyChars(Phone) as 'NumberOnly' from tblPeople

   Which will return:

   FirstName LastName Phone NumberOnly

   ----------------- ---------------- ------------- ---------------------

   John Doe [phone] 8885551212

Tips & Warnings

• With some simple modifications, this function can be changed to only return alpha characters or both alpha and numeric characters.

References

• SQL Authority: UDF--Function to Parse AlphaNumeric Characters from String