Solved

# REMOVE NUMBERS FROM A FIELD

Posted on 2011-05-12
307 Views
How would I query a field and remove all non-numeric characters and then put the new data back in the field?
0
Question by:Steve Samson

LVL 51

Expert Comment

create a function

create function RemoveNonNumeric(s varchar(max)) returns varchar(max)
begin
return replace(..... replace(replace(s,'A',''),'B',''),...),'Z','')
end

then use

update mytable
set col=dbo.RemoveNonNumeric(s)
0

LVL 41

Accepted Solution

Create the function below

then use it like this

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4\$', '^0-9')

or
SELECT dbo.fn_StripCharacters(yourcolumn, '^0-9') from yourtable

``````CREATE FUNCTION [dbo].[fn_StripCharacters] (
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX) AS
BEGIN
SET @MatchExpression =  '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String

END
``````
0

LVL 51

Expert Comment

if you say I cannot write that function, have a look at this

http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx

you can also write that function above as

create function RemoveNonNumeric(@s varchar(max)) returns varchar(max)
begin
declare @t varchar(max) = @s
set @t=replace(@t,'A','')
set @t=replace(@t,'B','')
...
set @t=replace(@t,'Z','')
return @t
end

0

LVL 41

Expert Comment

Although your question is not clear, you want the numbers to be removed or the non-numeric ones?

Either way the function above will work:

Alphabetic only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4\$', '^a-z')

Numeric only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4\$', '^0-9')

Alphanumeric only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4\$', '^a-z0-9')

Non-alphanumeric:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4\$', 'a-z0-9')
0

LVL 41

Expert Comment

with the update goes like this:

update yourtable set yourcolumn = dbo.fn_StripCharacters(yourcolumn, '^0-9')
0

Author Closing Comment

VERY SIMPLE AND SLICK WAY OF DOING IT.
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

### Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

#### 759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!