how to remove numbers from strings?

Question

Post reply

how to remove numbers from strings?

Viewing 15 posts - 16 through 30 (of 172 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you forgot the single quotes...

IF @TmpString NOT LIKE '[0-9]'

or if it was part of a large string, you'd add percent signs for the wildcard matches:

IF @TmpString NOT LIKE '%[0-9]%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Max Yasnytsky SSCrazy Points: 2462 More actions · Answer 2

Max Yasnytsky

SSCrazy

Points: 2462

October 17, 2007 at 9:09 pm

#742969

😀 it's the time of the day...

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

Doesn't that bring us back to Jeff Moden's function?

Max Yasnytsky SSCrazy Points: 2462 More actions · Answer 4

Yeah, but he is using some sort of prefilled table isn't he?

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Lowell (10/17/2007)
you forgot the single quotes...
IF @TmpString NOT LIKE '[0-9]'
or if it was part of a large string, you'd add percent signs for the wildcard matches:
IF @TmpString NOT LIKE '%[0-9]%'

Yep.

But should I leave some space for imperfections?

😀

But you were not attentive to details as well.

NOT LIKE '%[0-9]%'

is not the same as

LIKE '%[^0-9]%'

😛

And because @TmpString contains always single character NOT LIKE '[0-9]' is perfectly enough.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Max Yasnytskyy (10/17/2007)
I have found an interesintg function in msdb, seems like it is working much better ...

Ummm... this is an SQL Server 7/2000 forum... wanna tell us how well VARCHAR(MAX) and MSDB.dbo.ConvertToInt is going to work in SQL 7 or 2000? 😉

--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)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

mrpolecat (10/17/2007)
Tally is a table that you need to create with the column N. The fill it with the numbers 1 to whatever you think you need. This is from the Jeff Moden toolbox and is useful in many ways.
--copied shamelessly from a Jeff Moden Example
--Now, before we get to the solution, we need to make a well indexed table of sequential numbers. These "Tally" or "Numbers" tables are very powerful and can help do things in SQL Server 2000 as if we were using ROWNUM from SQL Server 2005. You should make a permanent Tally table as follows... yes, this is part of the solution for this and many other "impossible" tasks...
--===== 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) WITH FILLFACTOR = 100

Heh... That's what it's there for, MrPoleCat... but I sure do appreciate the honorable mention from both you and Greg.

--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)

Max Yasnytsky SSCrazy Points: 2462 More actions · Answer 8

Jeff Moden (10/17/2007)
Max Yasnytskyy (10/17/2007)
I have found an interesintg function in msdb, seems like it is working much better ...
Ummm... this is an SQL Server 7/2000 forum... wanna tell us how well VARCHAR(MAX) and MSDB.dbo.ConvertToInt is going to work in SQL 7 or 2000? 😉

Thanks for pointing out, as i didn't realize what server version this will run on.

I haven't ever used SQL Server 7 fortunately or unfortunately but what is wrong with using varchar(max) on those versions?

re: MSDB.dbo.ConvertToInt

we all sort of came to the conclusion that Sergiy's if @tmpString NOT LIKE '[0-9]' is optimal,

P.S. i'm mainly working with SQL 2005 and assume that everyone else too works on it:)

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Max Yasnytskyy (10/17/2007)

Jeff Moden (10/17/2007)

Max Yasnytskyy (10/17/2007)
P.S. i'm mainly working with SQL 2005 and assume that everyone else too works on it:)

I'm working on SQL2000 and playing with SQL2005.

Still have not found any single advantage of 2k5 over 2k.

Probably 2k5 is more comfortable for procedural language programmers, but for those who understand relational model and use to operate with datasets it's useless.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

Max Yasnytskyy (10/17/2007)
Yeah, but he is using some sort of prefilled table isn't he?

Yep... you should try it 😀 Works well on a lot of things and the guys did a pretty good job of explaining how to build one...

--===== 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) WITH FILLFACTOR = 100

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

GRANT SELECT ON dbo.Tally TO PUBLIC

--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)

Max Yasnytsky SSCrazy Points: 2462 More actions · Answer 11

Sergiy (10/18/2007)

Max Yasnytskyy (10/17/2007)

Jeff Moden (10/17/2007)
Max Yasnytskyy (10/17/2007)
P.S. i'm mainly working with SQL 2005 and assume that everyone else too works on it:)
I'm working on SQL2000 and playing with SQL2005.
Still have not found any single advantage of 2k5 over 2k.
Probably 2k5 is more comfortable for procedural language programmers, but for those who understand relational model and use to operate with datasets it's useless.

lets not forget about .net and advantages of 2005 when working with it

Max Yasnytsky SSCrazy Points: 2462 More actions · Answer 12

Jeff Moden (10/18/2007)
Max Yasnytskyy (10/17/2007)
Yeah, but he is using some sort of prefilled table isn't he?
Yep... you should try it 😀 Works well on a lot of things and the guys did a pretty good job of explaining how to build one...
--===== 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) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC

Are you trying to say that to solve this sort of problem any additional table is required?

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Max Yasnytskyy (10/18/2007)
Are you trying to say that to solve this sort of problem any additional table is required?

You've got a choice:

either dynamically generate the set of values every time and everywhere you need it by applying loops,

or have this set handy in a small static table shared by all users and all processes.

Which way do you think is more effective?

_____________
Code for TallyGenerator

Max Yasnytsky SSCrazy Points: 2462 More actions · Answer 14

it all depends on how the function is going to be used...

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 15

Max Yasnytskyy (10/17/2007)
Thanks for pointing out, as i didn't realize what server version this will run on.
I haven't ever used SQL Server 7 fortunately or unfortunately but what is wrong with using varchar(max) on those versions?
re: MSDB.dbo.ConvertToInt
we all sort of came to the conclusion that Sergiy's if @tmpString NOT LIKE '[0-9]' is optimal,
P.S. i'm mainly working with SQL 2005 and assume that everyone else too works on it:)

VARCHAR(MAX) did not exist until SQL Server 2005 and you can't assume that everyone is working with 2k5... especially when they ask the question on a 2k forum like this one 😉

--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)