Remove non numeric characters

Question

Post reply

Remove non numeric characters

norbertackerman

SSC Eights!

Points: 945
More actions
December 12, 2006 at 7:53 pm

#92052

How do I remove non-numeric characters from a field?
For example: BR-12023-55
Should become: 1202355
Thank you,
Norbert
meLearnASP.net

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

See the function Frank Kalis made at the following...

http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=153822

Before you make a generic post like this, you should do a search on the forum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

norbertackerman SSC Eights! Points: 945 More actions · Answer 2

norbertackerman

SSC Eights!

Points: 945

December 13, 2006 at 12:11 pm

#677878

Thank you. This is very helpful.

Norbert

meLearnASP.net

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

You bet... thank you for the feedback, Norbert...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

For once in my life I think I found a better solution than Franks >> and I'm a little surprised that you didn't think about it yourself :

IF EXISTS (SELECT * FROM dbo.SysObjects WHERE name = 'Numbers' AND XType = 'U' AND USER_NAME(uid) = 'dbo')

DROP TABLE Numbers

GO

CREATE TABLE dbo.Numbers (PkNumber INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, dude bit NULL)

GO

INSERT INTO dbo.Numbers (dude)

SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 CROSS JOIN (SELECT TOP 80 NULL AS A FROM master.dbo.spt_values) dt80

GO

ALTER TABLE dbo.Numbers

DROP COLUMN dude

GO

IF EXISTS (SELECT * FROM dbo.SysObjects WHERE name = 'fnExtractNumbers' AND XType = 'FN' AND USER_NAME(uid) = 'dbo')

DROP FUNCTION dbo.fnExtractNumbers

GO

CREATE FUNCTION dbo.fnExtractNumbers(@sText AS VARCHAR(1000))

RETURNS VARCHAR(1000)

AS

BEGIN

DECLARE @Result AS VARCHAR(1000)

SET @Result = ''

SELECT @Result = @Result + SUBSTRING(@sText, PkNumber, 1) FROM dbo.Numbers WHERE PkNumber <= LEN(@sText) AND ASCII(SUBSTRING(@sText, PkNumber, 1)) BETWEEN 48 AND 57

RETURN @Result

END

GO

IF EXISTS (SELECT * FROM dbo.SysObjects WHERE name = 'RemoveChars' AND XType = 'FN' AND USER_NAME(uid) = 'dbo')

DROP FUNCTION dbo.RemoveChars

GO

CREATE FUNCTION dbo.RemoveChars(@Input VARCHAR(1000))

RETURNS VARCHAR(1000)

BEGIN

DECLARE @pos INT

SET @Pos = PATINDEX('%[^0-9]%',@Input)

WHILE @Pos > 0

BEGIN

SET @Input = STUFF(@Input,@pos,1,'')

SET @Pos = PATINDEX('%[^0-9]%',@Input)

END

RETURN @Input

END

GO

/*

SELECT 'max int' AS TestName, dbo.fnExtractNumbers (' 2"14s df7gh4;8::36sdf4,7. ') AS Number

UNION ALL

SELECT 'min bigint > max int' AS TestName, dbo.fnExtractNumbers (' 2"14s df7gh4;8::36sdf4,8. ') AS Number

UNION ALL

SELECT 'not a number' AS TestName, dbo.fnExtractNumbers ('sdkjf slkfhsfhw`:"!/$(/&?"!$ ') AS Number

UNION ALL

SELECT 'NULL' AS TestName, dbo.fnExtractNumbers (NULL) AS Number

*/

GO

IF EXISTS (SELECT * FROM dbo.SysObjects WHERE name = 'TestRmChars' AND XType = 'U' AND USER_NAME(uid) = 'dbo')

DROP TABLE dbo.TestRmChars

GO

CREATE TABLE dbo.TestRmChars

(

SomeCol UNIQUEIDENTIFIER NOT NULL

)

INSERT INTO dbo.TestRmChars (SomeCol)

SELECT TOP 1000000 NEWID() FROM master.dbo.SysColumns C1 CROSS JOIN master.dbo.SysColumns C2

GO

--shoot the data into ram

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DECLARE @a VARCHAR(100)

SELECT @a = SomeCol FROM dbo.TestRmChars

GO

DECLARE @sTime AS DATETIME

DECLARE @tmp AS VARCHAR(1000)

SET @sTime = GETDATE()

SELECT @tmp = dbo.fnExtractNumbers (SomeCol) FROM dbo.TestRmChars

PRINT DATEDIFF (MS, @sTime, GETDATE())

--121.563 S

SET @sTime = GETDATE()

SELECT @tmp = dbo.RemoveChars (SomeCol) FROM dbo.TestRmChars

PRINT DATEDIFF (MS, @sTime, GETDATE())

--234.593 S

--CLEANUP

IF EXISTS (SELECT * FROM dbo.SysObjects WHERE name = 'TestRmChars' AND XType = 'U' AND USER_NAME(uid) = 'dbo')

DROP TABLE dbo.TestRmChars

GO

IF EXISTS (SELECT * FROM dbo.SysObjects WHERE name = 'RemoveChars' AND XType = 'FN' AND USER_NAME(uid) = 'dbo')

DROP FUNCTION dbo.RemoveChars

GO

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I think my version could even be faster by switching the ascii() between 48 and 57 to substring between '0' AND '9'

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

After some testing it appears that the ascii version is constantly better, by almost 10%. Anyone wanted a proof that comparing strings is longer than comparing numbers ?!?!

Robert Davis One Orange Chip Points: 28027 More actions · Answer 7

Here is a simpler version of that function:

CREATE FUNCTION dbo.RemoveChars(@Input VARCHAR(1000))

RETURNS VARCHAR(1000)

BEGIN

While PatIndex('%[^0-9]%', @Input) > 0

Begin

Set @Input = Stuff(@Input, PatIndex('%[^0-9]%', @Input), 1, '')

End

RETURN @Input

END

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us

SSC Guru

Points: 294069

December 14, 2006 at 4:59 pm

#678237

Is it faster than the original version?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Remi... I think you may have confused a couple of people because you didn't include any documention in your test script to identify the fact that most of your code was just setting up the Numbers table and the test data table.

And, I did "think of it"... I was just being lazy

Robert... heck of a nice try and clever code... but's it's slower than Frank's original code.

And, with all that in mind, let the races begin! Pay particular attention to the sections of code labeled "Jeff's Function"

First, like Remi did in his code, we need to setup both a "numbers" table (I call it a "Tally" table) and a table with a good quantity of test data in it... here's how to do that (Note: If you don't already have a permanent Tally or Numbers table, now's the time to pay attention and copy some code, folks! )...

--=================================================================================================

-- Create the "Tally" table that we need for some of the functions

--=================================================================================================

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

GO

--=================================================================================================

-- Create and populate a test table with 100K rows.

-- Column "ADate" has a range of >=01/01/2000 <01/01/2010

-- That's ten years worth of dates.

--=================================================================================================

SELECT TOP 100000

IDENTITY(INT,1,1) AS RowNum,

CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT) AS UserID,

CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))

+ CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) AS SomeValue,

'a1s9dsad124325143gffdfd4dgsf' AS MixedCharacters,

'Still another column just for proofing' AS StillAnother,

CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS DECIMAL(18,9)) AS SomeNumber,

CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME) AS ADate

INTO dbo.BigTest

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== A table is not properly formed unless a Primary Key has been assigned

ALTER TABLE dbo.BigTest

ADD PRIMARY KEY CLUSTERED (RowNum)

GO

... Now, we'll create a copy of everyone's function using that person's name as a part of the function name...

--=================================================================================================

-- Create all the functions offered so far

--=================================================================================================

--===== Create Frank's original function

CREATE FUNCTION dbo.FranksFunction(@Input varchar(1000))

RETURNS VARCHAR(100)

BEGIN

DECLARE @pos INT

SET @Pos = PATINDEX('%[^0-9]%',@Input)

WHILE @Pos > 0

BEGIN

SET @Input = STUFF(@Input,@pos,1,'')

SET @Pos = PATINDEX('%[^0-9]%',@Input)

END

RETURN @Input

END

GO

--===== Create Remi's function

-- (Table name changed from Numbers to Tally)

-- (Column changed from PKNumber to N)

-- (Everything else the same)

CREATE FUNCTION dbo.RemisFunction(@sText AS VARCHAR(1000))

RETURNS VARCHAR(1000)

AS

BEGIN

DECLARE @Result AS VARCHAR(1000)

SET @Result = ''

SELECT @Result = @Result + SUBSTRING(@sText, N, 1)

FROM dbo.Tally

WHERE N <= LEN(@sText)

AND ASCII(SUBSTRING(@sText, N, 1)) BETWEEN 48 AND 57

RETURN @Result

END

GO

--===== Create Remi's modified function

-- (Table name changed from Numbers to Tally)

-- (Column changed from PKNumber to N)

-- (Everything else the same)

CREATE FUNCTION dbo.RemisFunctionMod(@sText AS VARCHAR(1000))

RETURNS VARCHAR(1000)

AS

BEGIN

DECLARE @Result AS VARCHAR(1000)

SET @Result = ''

SELECT @Result = @Result + SUBSTRING(@sText, N, 1)

FROM dbo.Tally

WHERE N <= LEN(@sText)

AND SUBSTRING(@sText, N, 1) BETWEEN '0' AND '9'

RETURN @Result

END

GO

--===== Create Jeff's function

CREATE FUNCTION dbo.JeffsFunction(@sText AS VARCHAR(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Result AS VARCHAR(8000)

SELECT @Result = ISNULL(@Result,'') + SUBSTRING(@sText, N, 1)

FROM dbo.TALLY

WHERE N <= LEN(@sText)

AND SUBSTRING(@sText, N, 1) LIKE '[0-9]'

RETURN @Result

END

GO

--===== Create Robert's Function

CREATE FUNCTION dbo.RobertsFunction(@Input VARCHAR(1000))

RETURNS VARCHAR(1000)

BEGIN

While PatIndex('%[^0-9]%', @Input) > 0

Begin

Set @Input = Stuff(@Input, PatIndex('%[^0-9]%', @Input), 1, '')

End

RETURN @Input

END

GO

... and finally, let's test all of those functions on level playing ground...

--=================================================================================================

-- Test each function with clearing the cache and dropping clean buffers to keep anyone from

-- having an unfair advantage or one function from interferring with another.

-- RUN FROM HERE DOWN AS OFTEN AS YOU LIKE!!!!!

--=================================================================================================

PRINT '--===== Frank''s function'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DECLARE @Bitbucket VARCHAR(8000)

DECLARE @StartTime DATETIME

SET @StartTime = GETDATE()

SELECT @Bitbucket = dbo.FranksFunction(MixedCharacters)

FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'

PRINT REPLICATE('=',92)

GO

PRINT '--===== Remi''s function'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DECLARE @Bitbucket VARCHAR(8000)

DECLARE @StartTime DATETIME

SET @StartTime = GETDATE()

SELECT @Bitbucket = dbo.RemisFunction(MixedCharacters)

FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'

PRINT REPLICATE('=',92)

GO

PRINT '--===== Remi''s modified function'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DECLARE @Bitbucket VARCHAR(8000)

DECLARE @StartTime DATETIME

SET @StartTime = GETDATE()

SELECT @Bitbucket = dbo.RemisFunctionMod(MixedCharacters)

FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'

PRINT REPLICATE('=',92)

GO

PRINT '--===== Jeff''s (me) function'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DECLARE @Bitbucket VARCHAR(8000)

DECLARE @StartTime DATETIME

SET @StartTime = GETDATE()

SELECT @Bitbucket = dbo.JeffsFunction(MixedCharacters)

FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'

PRINT REPLICATE('=',92)

GO

PRINT '--===== Robert''s function'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DECLARE @Bitbucket VARCHAR(8000)

DECLARE @StartTime DATETIME

SET @StartTime = GETDATE()

SELECT @Bitbucket = dbo.RobertsFunction(MixedCharacters)

FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'

PRINT REPLICATE('=',92)

GO

...Here's the output from my server at work...

--===== Frank's function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

5686 Milliseconds

============================================================================================

--===== Remi's function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

4923 Milliseconds

============================================================================================

--===== Remi's modified function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

4906 Milliseconds

============================================================================================

--===== Jeff's (me) function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

3780 Milliseconds

============================================================================================

--===== Robert's function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

6266 Milliseconds

============================================================================================

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

nigel. SSChampion Points: 11636 More actions · Answer 10

and for even more fun pass the pattern for the like operator as a parameter to the function:

CREATE FUNCTION dbo.JeffsFunction(@sText AS VARCHAR(8000),@pattern varchar(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Result AS VARCHAR(8000)

SELECT @Result = ISNULL(@Result,'') + SUBSTRING(@sText, N, 1)

FROM dbo.TALLY

WHERE N <= LEN(@sText)

AND SUBSTRING(@sText, N, 1) LIKE @pattern

RETURN @Result

END

GO

Nigel
How to post questions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Way to go Nigel!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 12

for whats it worth franks function runs fastest in my sql express inastallatin on my creaking lap top

(11000 row(s) affected)

(100000 row(s) affected)

--===== Frank's function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

17976 Milliseconds

============================================================================================

--===== Remi's function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

18463 Milliseconds

============================================================================================

--===== Remi's modified function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

18860 Milliseconds

============================================================================================

--===== Jeff's (me) function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

18826 Milliseconds

============================================================================================

www.sql-library.com[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Thanks for the feedback Jules... I always wonder if anyone actually runs these things

Like I said, how long does it take on YOUR machine... your experiment is proof positive that "tuning" can be (is be?) very machine and RDBMS dependent.

Anyone else care to share their findings? I know I'd sure like to see what gives on different machines...

BTW, the server at work uses a SAN, has four 2g processors, runs the Enterprise Edition of SQL 2000, and has 8 gig of ram. Not a real monster or anything...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

John McC Hall of Fame Points: 3410 More actions · Answer 14

This is our server at work (Twin 1.2Mhz PIII's & 1Gb Ram)

--===== Frank's function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

11343 Milliseconds

============================================================================================

--===== Remi's function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

9593 Milliseconds

============================================================================================

--===== Remi's modified function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

9436 Milliseconds

============================================================================================

--===== Jeff's (me) function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

8140 Milliseconds

============================================================================================

--===== Robert's function

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

13013 Milliseconds

============================================================================================

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: