• Status: Solved
• Priority: Medium
• Security: Public
• Views: 311

REMOVE NUMBERS FROM A FIELD

How would I query a field and remove all non-numeric characters and then put the new data back in the field?
0
Steve Samson
• 3
• 2
1 Solution

Sr. System AnalystCommented:
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

Commented:
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

Sr. System AnalystCommented:
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

Commented:
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

Commented:
with the update goes like this:

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

Author Commented:
VERY SIMPLE AND SLICK WAY OF DOING IT.
0

Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.