SQL–Function To Remove Non-numeric Characters

Such a simple task in hindsight, but if you’re scratching your head about how to remove non-numeric characters from a string, this function should help you.

The function uses the in built STUFF and PATINDEX functions to identify a characters which aren’t numeric (0-9) and replace each non-numeric character with an empty string (‘’).

 

   1: CREATE Function [xFnRemoveNonNumericCharacters](@strText VARCHAR(1000))

   2: RETURNS VARCHAR(1000)

   3: AS

   4: BEGIN

   5:     WHILE PATINDEX('%[^0-9]%', @strText) > 0

   6:     BEGIN

   7:         SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')

   8:     END

   9:     RETURN @strText

  10: END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s