How do I extract numbers from a text field?

Question

Post reply

How do I extract numbers from a text field?

Viewing 7 posts - 16 through 21 (of 21 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Now you know why I didn't want to show my function... :crying:

The varchar(max) discussion is an ongoing subject on various threads from time to time but I didn't think a bit about the split function.

Now I know what I'm going to do tomorrow at work: fire my "source code search engine" with "varchar(max)" as search string and see where it's really required.

If you don't hear anything from my side for the rest of the year, you know why. 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

lmu92 (11/15/2009)

Now you know why I didn't want to show my function... :crying:

Heh... now you know why I always post mine... someone might improve on it and it's my gain. 🙂 And don't forget... your code is exactly the same way that I used to write mine so I'm in the same barrel with ya. 😉

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

Paul White SSC Guru Points: 150457 More actions · Answer 3

Jeff Moden (11/15/2009)

The second "optimization" isn't really an optimization... it's a realization of how much the use of VARCHAR(MAX) sucks the life out of code. My speculation on that was that VARCHAR(MAX) is always treated as an "out of row" datatype but it was just a speculation. Others have insisted that VARCHAR(MAX) will be "in row" if it fits. The bottom line is, whatever... VARCHAR(MAX) performs much slower than otherwise equivalent code that uses VARCHAR(8000). In English, don't use VARCHAR(MAX) if VARCHAR(8000) will do.

Just to be absolutely clear on this one: MAX datatypes are stored exactly as for non-MAX types unless the data length is more than 8000 bytes, in which case they are stored exactly as for TEXT/NTEXT/IMAGE, as appropriate.

MAX type variables are quite different: SQL Server has to account for the fact that they could go over 8000 bytes at any time (not just when persisted). The different code paths within the engine can result in MAX type variables performing less well than non-MAX type variables.

As for string splitting? See http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html for Flo's in-depth performance tests and results. (Spoiler: use .NET code)

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

All good points (have you got an MS URL that I could read up on or is that info in BOL?). The result is... MAX is a wee bit performance challenged unless there's no other way. 😛

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

Paul White SSC Guru Points: 150457 More actions · Answer 5

Jeff Moden (11/16/2009)

All good points (have you got an MS URL that I could read up on or is that info in BOL?). The result is... MAX is a wee bit performance challenged unless there's no other way. 😛

I'll see if I can dig something up tomorrow (is very late here now) - but the general storage stuff is covered well in Kalen Delaney's 2005 Storage Engine and the 2008 edition (forget the exact name offhand).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Thanks Paul. I found a good one... probably one of the ones that you would have pointed out.

http://msdn.microsoft.com/en-us/library/ms189087.aspx

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

Paul White SSC Guru Points: 150457 More actions · Answer 7

Yep that covers it, but it's maybe not the clearest or most comprehensive page ever added to Books Online 🙂

There's a load more interesting stuff around the storage of the MAX types - if only someone would cover it in an article...;-) 😛 :w00t:

The impact of using MAX-typed variables is similarly interesting too...:cool:

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi