Help writing function to get decimal from string

Question

Help writing function to get decimal from string

Viewing 15 posts - 31 through 45 (of 54 total)

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

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

Chris Morris (11/18/2008)
Jeff Moden (11/17/2008)
Chris Morris (11/17/2008)
Jeff, perfick solution as always!
Serious or sarcasm? Can't tell from here.
Aw Jeff you should know me better than that!
This...
WHERE t.N BETWEEN CHARINDEX('(',@String) +1
AND CHARINDEX(')',@String) -1
AND SUBSTRING(@String,t.N,1) LIKE '[0-9.]'
...does exactly what the OP wants; no more, no less, and as efficient as it gets. There are good solutions and perfick ones, and this is a prime example of a perfick one.
Cheers
ChrisM

Man... I'm sorry for the doubt... I'm just a little gun-shy after a lengthy rowe about RBAR only to find out the solution was for a GUI and had to be one row at a time. Thought you were talking about me missing the idea of a case sensitive server and thought "Ok... I'm wrong again".

Sorry for the doubt, Chris, and thanks for the great compliment. Heh, it's a nice change for the last three days. 🙂

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

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

Dooza (11/18/2008)
am so glad my server ain't screwed!
Steve

Man, me too! Thanks Chris. Sorry I forgot about the server maybe being case sensitive... that's the good thing about forums, more than one suggestion/solution.

As a side bar, I just can't imagine someone doing that to a whole server. I can understand building some columns in a table as being case senstive, but the whole server?

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

Dooza-770962 Ten Centuries Points: 1021 More actions · Answer 3

Jeff, your solution is working perfikly for me, thank you once again for the excellent help and advice. And thank you to Seph and Chris too!

Steve

Dooza-770962 Ten Centuries Points: 1021 More actions · Answer 4

Jeff Moden (11/18/2008)
As a side bar, I just can't imagine someone doing that to a whole server. I can understand building some columns in a table as being case senstive, but the whole server?

The server is used for our in house sales system, its called Streamline from Geac in New Zealand. I have only been here 1 and a half years, but the system has been here for 7 years. Its a pretty complicated setup, and I am just doing little mods here and there to be able to get data out for specific needs. I can only assume its this way for a reason, and was setup like this by the developers.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Dooza (11/18/2008)
Jeff, your solution is working perfikly for me, thank you once again for the excellent help and advice. And thank you to Seph and Chris too!
Steve

You're welcome Steve, thanks for the feedback!

Jeff:

Man... I'm sorry for the doubt... I'm just a little gun-shy after a lengthy row

about RBAR only to find out the solution was for a GUI and had to be one

row at a time. Thought you were talking about me missing the idea of a case

sensitive server and thought "Ok... I'm wrong again".

I'm sorry mate, I've reread that post and I can see where you're coming from. My bad.

How often have you spent hours, even days, on a solution for a desperate punter only to find they've not bothered to post all the details of the problem - making your hard work completely redundant?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Dooza (11/18/2008)
... and was setup like this by the developers.

Heh... of that, I have no doubt. Thanks for the feedback, Steve. 🙂

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

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

Chris Morris (11/18/2008)
How often have you spent hours, even days, on a solution for a desperate punter only to find they've not bothered to post all the details of the problem - making your hard work completely redundant?
Cheers
ChrisM

The really good part about it all is that no matter what goes wrong, I always learn something new even if it's only how to "read minds" a little bit better. 😀

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

Garadin One Orange Chip Points: 29613 More actions · Answer 8

Jeff Moden (11/18/2008)

The really good part about it all is that no matter what goes wrong, I always learn something new even if it's only how to "read minds" a little bit better. 😀

Oh yeah? What am I thinking about right now?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

Garadin (11/18/2008)

Jeff Moden (11/18/2008)
The really good part about it all is that no matter what goes wrong, I always learn something new even if it's only how to "read minds" a little bit better. 😀
Oh yeah? What am I thinking about right now?

Heh... that you're thinking I'm right about that... :hehe:

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

Garadin One Orange Chip Points: 29613 More actions · Answer 10

Actually, the answer was coffee. Keep practicing!

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

BWAA-HAA! I guess it's because I need more coffee, as well! Maybe that qualifies. 😛

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Garadin (11/18/2008)
Actually, the answer was coffee. Keep practicing!

Coffee? I was projecting an image of a well-hopped bitter on my desk but sadly it remained exactly that - an image! 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 13

I try not to torture myself with thoughts of good beer during the day. That said, any particular bitters worth trying out? I'm a hophead myself, but most often I stick with PA or IPA's. I actually brewed a pretty good bitter about 6 months ago. This thread is now officially off topic... but at least we fixed the problem first :P.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Dooza-770962 Ten Centuries Points: 1021 More actions · Answer 14

Its nice seeing a post turn off-topic without being shut down immediately.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

Garadin (11/18/2008)
I try not to torture myself with thoughts of good beer during the day. That said, any particular bitters worth trying out? I'm a hophead myself, but most often I stick with PA or IPA's. I actually brewed a pretty good bitter about 6 months ago. This thread is now officially off topic... but at least we fixed the problem first :P.

Beer? There's a small pub here in Reading, UK which offers a wide range of excellent beers, usually including three or four from West Berkshire Breweries. Dr Hexter's Healer and Dr Hexter's Wedding Ale are astonishingly good, a perfect balance between rich, complex, juicy malt and aromatic citrus hoppiness. Perfick when it's sunny. Bottled ales? Fuller's bottled ales are superb - ESB, Golden Pride, London Porter...blimey, can't wait until 5.30 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden