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

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
Asked:
Steve Samson
  • 3
  • 2
1 Solution
 
HainKurtSr. 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
 
ralmadaCommented:
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

Open in new window

0
 
HainKurtSr. 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
ralmadaCommented:
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
 
ralmadaCommented:
with the update goes like this:

update yourtable set yourcolumn = dbo.fn_StripCharacters(yourcolumn, '^0-9')
0
 
Steve SamsonAuthor Commented:
VERY SIMPLE AND SLICK WAY OF DOING IT.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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