how to remove numbers from strings?

Question

how to remove numbers from strings?

Viewing 15 posts - 91 through 105 (of 172 total)

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

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

Why would you think that that would remain unquestioned, when it was simply a repeat of the personal attack, that again, has no place in this forum?

Sorry, David, I sure didn't mean for it to be personal... Both statements fit into the idea that those types of tools are provided as a convenience for folks that can't figure out the T-SQL to do it. I just said it the wrong way and once I realized that people took it the wrong way, I took the time to explain it what I really meant... and that I wouldn't do that again...

I'll have a Pivot table example and test data on the 2k side of the house tomorrow...

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

David McFarland SSChampion Points: 11815 More actions · Answer 2

Jeff Moden (10/21/2007)
Lowell, here's the code, one more time... would you and David please do the honors? Thanks, guys...

I simply added the line for the starttime to the original code, as I didn't want to have to reinsert CR/LFs all over again (the code window here doesn't carry them over on a copy on my machine, guessing they are just returning linefeeds, perhaps, but not going to pull out the hex editor now to find out). I ran it a couple of times.

ETA: Tests were run in SSMS 2005 using SQL Server Standard Edition, build 3054.

Creating test table...

(1000000 row(s) affected)

00:00:11:343 Duration (hh:mi:ss:mmm)

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

INNER JOIN method...

(1000000 row(s) affected)

00:00:30:000 Duration (hh:mi:ss:mmm)

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

OverParition method...

(1000000 row(s) affected)

00:00:30:483 Duration (hh:mi:ss:mmm)

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

Creating test table...

(1000000 row(s) affected)

00:00:11:767 Duration (hh:mi:ss:mmm)

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

INNER JOIN method...

(1000000 row(s) affected)

00:00:29:293 Duration (hh:mi:ss:mmm)

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

OverParition method...

(1000000 row(s) affected)

00:00:29:377 Duration (hh:mi:ss:mmm)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 3

There was a feeling that something is not right.

Now we have 2 point something times difference.

About the value I expected.

But it still should be under 2 times because there is a common part in both queries.

Over 2 times difference shows that even "old good" query engine is implemented in SQL2k5 worse than in SQL2k.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Heh... jury is still out on 2 times... it's starting to look a lot like the hardware or the installation may also make a huge difference. Davids times came out almost exactly the same... let's see what Lowell's run comes up with.

Lowell and David, if you get the chance and have the inclination, would you post your hardware configs? Thanks, guys.

David, they are, indeed "line feeds". I get around it by pasting the code into MS Word and replacing ^l (circumflex with lower case "L") with ^p (circumflex with lower case "P"). Still doesn't keep leading spaces but that's no biggee...

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

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Jeff Moden (10/21/2007)
Heh... jury is still out on 2 times... it's starting to look a lot like the hardware or the installation may also make a huge difference. Davids times came out almost exactly the same... let's see what Lowell's run comes up with.

I believe Lowell pointed on the reason - different drivers using to return recordsets from different servers.

Pure test should insert data into temp table, not to return to fromt end.

_____________
Code for TallyGenerator

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

Sergiy (10/21/2007)
Jeff Moden (10/21/2007)
Heh... jury is still out on 2 times... it's starting to look a lot like the hardware or the installation may also make a huge difference. Davids times came out almost exactly the same... let's see what Lowell's run comes up with.
I believe Lowell pointed on the reason - different drivers using to return recordsets from different servers.
Pure test should insert data into temp table, not to return to fromt end.

That would be the last bit of code I posted... David, did you run the first code (select to screen) or the last code (insert into temp table)? In the tests that Lowell did, that made a huge difference...

--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: 1004340 More actions · Answer 7

BTW... Sergiy, do you have convenient access to a 2k5 box that you could run these tests on? Would be real good if we had a 3rd set of numbers...

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

Lowell SSC Guru Points: 323518 More actions · Answer 8

My Dev Server for testing:

Windows 2000 Advanced Server SP4

1 Gig ram

AMD Athlon 64 Processor 3400+

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

SQL2000/QA

Creating test table...

00:00:21:467 Duration (hh:mi:ss:mmm)

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

INNER JOIN method...

00:00:05:737 Duration (hh:mi:ss:mmm)

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

OverParition method...

[Not Applicable]

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

SQL2000/QA Second Pass

Creating test table...

00:00:09:403 Duration (hh:mi:ss:mmm)

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

INNER JOIN method...

00:00:03:000 Duration (hh:mi:ss:mmm)

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

SQL2005/QA

Creating test table...

00:00:09:030 Duration (hh:mi:ss:mmm)

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

INNER JOIN method...

00:00:03:533 Duration (hh:mi:ss:mmm)

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

OverParition method...

00:00:08:907 Duration (hh:mi:ss:mmm)

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

SQL2005/QA Second Pass

Creating test table...

00:00:07:953 Duration (hh:mi:ss:mmm)

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

INNER JOIN method...

00:00:02:437 Duration (hh:mi:ss:mmm)

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

OverParition method...

00:00:07:670 Duration (hh:mi:ss:mmm)

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

SQL2005/SSMS

Creating test table...

00:00:48:500 Duration (hh:mi:ss:mmm)

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

INNER JOIN method...

00:00:03:360 Duration (hh:mi:ss:mmm)

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

OverParition method...

00:00:08:797 Duration (hh:mi:ss:mmm)

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

SQL2005/SSMS repeated, because

the temp table took so long:

Creating Temp Table

00:00:09:017 Duration (hh:mi:ss:mmm)

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

INNER JOIN method...

00:00:02:437 Duration (hh:mi:ss:mmm)

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

OverParition method...

00:00:07:593 Duration (hh:mi:ss:mmm)

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

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!

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

Awesome, Lowell... thanks!

Sergiy was right... he expected about 2 times faster... sometimes, almost 3 times faster for the Inner Join.

You guys are the best! I'll start working on a pivot table example tomorrow when I get home.

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

David McFarland SSChampion Points: 11815 More actions · Answer 10

Jeff Moden (10/21/2007)
That would be the last bit of code I posted... David, did you run the first code (select to screen) or the last code (insert into temp table)? In the tests that Lowell did, that made a huge difference...

I ran the first code, and I believe I stated as such. That's exactly how it is used on our end, not sent to temp tables, as the stored procs in which we use the Over() function are the datasources for various reports. In that situation, both methods run in the same timeframe. In our real world uses, the run time is under a second. If my requirements are to write something to temp tables, I'll write what I consider the best ("best" again being subjective and highly dependent on the situation, as I noted earlier) code for that. In the situations where we use Over(), it works like a charm, is easily modifiable, quicker to code, and less prone to typos. My original code that I threw together had zero typos the first time I ran it. Sergiy's example had three separate errors, which is not a slam on Sergiy, as I have typos, miss columns, forget group bys, etc., all of the time. To be fair, two of the three errors would have been just as likely in the Over() query (I just didn't happen to make them, but I certainly have in the past), but not the third, the Group By. It's not necessarily the complexity of code that causes errors to be introduced, sometimes it's just the amount of code. Same results, same timeframe, less code? I'm in.

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Jeff Moden (10/21/2007)
BTW... Sergiy, do you have convenient access to a 2k5 box that you could run these tests on? Would be real good if we had a 3rd set of numbers...

I'm sick in bed with my old laptop.

Don't have access to any server at all.

:blink:

And it was long weekend here!

Bloody hell!!! :crazy:

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

David McFarland (10/21/2007)
I ran the first code, and I believe I stated as such. That's exactly how it is used on our end, not sent to temp tables, as the stored procs in which we use the Over() function are the datasources for various reports. In that situation, both methods run in the same timeframe. In our real world uses, the run time is under a second. If my requirements are to write something to temp tables, I'll write what I consider the best ("best" again being subjective and highly dependent on the situation, as I noted earlier) code for that. In the situations where we use Over(), it works like a charm, is easily modifiable, quicker to code, and less prone to typos. My original code that I threw together had zero typos the first time I ran it. Sergiy's example had three separate errors, which is not a slam on Sergiy, as I have typos, miss columns, forget group bys, etc., all of the time. To be fair, two of the three errors would have been just as likely in the Over() query (I just didn't happen to make them, but I certainly have in the past), but not the third, the Group By. It's not necessarily the complexity of code that causes errors to be introduced, sometimes it's just the amount of code. Same results, same timeframe, less code? I'm in.

Ummm... understood... but would you mind running the last rendition just so we're not making the mistake of single machine bias? Thanks, Dave.

--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: 1004340 More actions · Answer 13

Sergiy (10/22/2007)
I'm sick in bed with my old laptop.
Don't have access to any server at all.
:blink:
And it was long weekend here!
Bloody hell!!! :crazy:

Heh... I'm sick too! Sick of working on weekends because of other people's poor planning 😛 Just now finishing a gotta-have-it-now project that's due in 5 1/2 hours. And they didn't even give me the requirements until late on Friday... I don't think I'm going to let them do this to me again...:D

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

Sergiy SSC Guru Points: 110208 More actions · Answer 14

Jeff Moden (10/22/2007)
Heh... I'm sick too! Sick of working on weekends because of other people's poor planning 😛 Just now finishing a gotta-have-it-now project that's due in 5 1/2 hours. And they didn't even give me the requirements until late on Friday... I don't think I'm going to let them do this to me again...:D

Lucky bastard! 😛

I'm still getting the requirements, and it's 2 weeks after the project is deployed to PRODUCTION!!!

(there was Staging for more than 2 month...)

:w00t:

_____________
Code for TallyGenerator

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

HEH!! Now, THAT's entertainment 😀

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