Help writing function to get decimal from string

Question

Help writing function to get decimal from string

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

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

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

I thought I'd throw my 2 cents in here... both Seth and Chris did a cool job on this. But, being ever vigilant for every micro-second of performance, I've found that REPLACE is a wee bit more expensive than a couple of CHARINDEX's. Further, we know that the data HAS to be between the FIRST set of parenthesis so we don't need "N" to look at all the characters in the string for a tiny tiny bit of extra savings. All the optimization I'm talking about actually makes the code a wee bit simpler because there's no need for nested selects, etc.

Last, but not least (although it probably doesn't matter... just being "Byte Conscious"), DECIMAL(10,2) takes 9 bytes of memory... DECIMAL(9,2) only takes 5...

Also, Dooza... look at how I made the test table and data. You can get some really quick and actually tested answers if you present your example data as code in that fashion. Please see the link in my signature below for how to easily do that for even more complicated data... makes life real easy for folks like us and folks tend to do those types of posts first because they don't have to work at it so hard.

Ok... off the soap-box... here's my suggestion...

--===== Create a test table and populate it with data from the original post.

-- THIS IS NOT PART OF THE SOLUTION!

CREATE TABLE #yourtable (GroupDesc VARCHAR(50))

INSERT INTO #yourtable (GroupDesc)

SELECT 'Goboland Steel Gobo' UNION ALL

SELECT 'Procolor (39.0)' UNION ALL

SELECT 'Rosco J1 (43.?)' UNION ALL

SELECT 'Doughty 2, 3 & 4 (70.1)' UNION ALL

SELECT 'Clay Paky Spares (no PDF)' UNION ALL

SELECT 'DBX (A11.0)' UNION ALL

SELECT 'Le Mark (Stocked) (no PDF)' UNION ALL

SELECT 'Le Mark (non stocked) (no PDF)'

GO

CREATE FUNCTION dbo.GetFirstBracketDecimal

(@String VARCHAR(50))

RETURNS DECIMAL(9,2) --Uses 4 bytes less than (10,2)

AS

BEGIN

--===== Declare local variables

DECLARE @WorkString VARCHAR(50)

--===== Concatenate only numeric characters found in the first set of parentheses

SELECT @WorkString = COALESCE(@WorkString,'') + SUBSTRING(@String,t.N,1)

FROM dbo.Tally t

WHERE t.N BETWEEN CHARINDEX('(',@String) +1

AND CHARINDEX(')',@String) -1

AND SUBSTRING(@String,t.N,1) LIKE '[0-9.]'

--===== Return the found value converting things where nothing was found to 0.

RETURN ISNULL(@WorkString,0)

END

GO

--===== Test the function using the test data from the original post

SELECT GroupDesc, dbo.GetFirstBracketDecimal(GroupDesc) AS FoundDecimalValue

FROM #yourtable

GO

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

Oh yeah... almost forgot... the reason why the WHERE clause actually works is because of my friend, the NULL value. It's not comparable using most relational operators unless someone made the very sorry mistake of messing with the server settings on how NULLs are treated. Even if that mistake is made, it'll still work, though... read on...

So, when there are NO parentheses, the WHERE clause ends up looking like this...

WHERE t.N BETWEEN NULL

AND NULL

AND SUBSTRING(@String,t.N,1) LIKE '[0-9.]'

That means that the "pseudo-cursor" formed by the Tally table doesn't actually do any iterations and, unlike most cases, is actually faster in finding nothing.

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

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

Hi Jeff, thank you for suggestion, as always I am grateful for your help and the help of others, I will endeavour to make it easier to get help in the future by following your guidelines.

I have tried to do as you suggested, but am having problems with the creation of the tally table. I already have one called Nbrs from a previous exercise, but for some reason I get this error:

Msg 208, Level 16, State 1, Line 18

Invalid object name 'Master.dbo.SysColumns'.

Msg 208, Level 16, State 1, Line 18

Invalid object name 'Master.dbo.SysColumns'.

I was doing the second example from here: http://www.sqlservercentral.com/articles/TSQL/62867/

I have been using my Nbrs table in the UDF, but was about to implement your changes.

Steve

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

I'll take a look, Dooza...

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

Dooza... what version of SQL Server are you using? Also, just to be absolutely clear, would you post the exact code that you're using so I can try to find out what's wrong? Better yet... try the Tally table creation code I posted above for Seth and lemme know how that works. I still need you to verify the version of SQL Server you are using...

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

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

Version: 8.00.760

I got exactly the same error which is why I tried the other page which was originally given in the first reply.

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

Oh and the code

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

-- Setup

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

USE tempdb --DB that everyone has where we can cause no harm

SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed

DECLARE @StartTime DATETIME --Timer to measure total duration

SET @StartTime = GETDATE() --Start the timer

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

-- Create and populate a Tally table

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

--===== Conditionally drop

IF OBJECT_ID('dbo.Tally') IS NOT NULL

DROP TABLE dbo.Tally

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

--===== Let the public use it

GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

--===== Display the total duration

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

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

Dooza-770962

Ten Centuries

Points: 1021

November 14, 2008 at 10:19 am

#898733

And the collation is Latin1_General_BIN

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

Try the following...

SELECT COUNT(*) FROM Master.dbo.SysColumns

If it doesn't work, there are one of two problems... either you don't have the privs to read from the master database (very unusual) or your master database is screwed.

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

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

Msg 208, Level 16, State 1, Line 2

Invalid object name 'Master.dbo.SysColumns'.

I think my master database is screwed 🙁

We did have some problems a few weeks back with a maintenance routine trying to do something and it kept on having issues. I will get our head it chap to take a look. Thanks for the heads up on this!

Steve

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

Dooza (11/14/2008)
Msg 208, Level 16, State 1, Line 2
Invalid object name 'Master.dbo.SysColumns'.
I think my master database is screwed 🙁
We did have some problems a few weeks back with a maintenance routine trying to do something and it kept on having issues. I will get our head it chap to take a look. Thanks for the heads up on this!
Steve

Man, sorry about the bad news. Let us know what becomes of it if you can... I'd like to find out not only if I'm right about saying it's screwed, but what you or they did to fix it. Thanks, 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)

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

Jeff Moden (11/14/2008)
Try the following...
SELECT COUNT(*) FROM Master.dbo.SysColumns
If it doesn't work, there are one of two problems... either you don't have the privs to read from the master database (very unusual) or your master database is screwed.

It could be case-sensitivity.

Dooza, find the table in the master database in EM and match the case in your query. Our servers are case-sensitive and it's often responsible for this type of problem.

Jeff, perfick solution as always!

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

Chris Morris (11/17/2008)
Jeff, perfick solution as always!

Serious or sarcasm? Can't tell from here.

Anyway, yeah, I forget about case sensitive servers. Thanks for the pickup on that.

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

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

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

^{“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

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

Chris Morris (11/17/2008)
Jeff Moden (11/14/2008)
Try the following...
SELECT COUNT(*) FROM Master.dbo.SysColumns
If it doesn't work, there are one of two problems... either you don't have the privs to read from the master database (very unusual) or your master database is screwed.
It could be case-sensitivity.
Dooza, find the table in the master database in EM and match the case in your query. Our servers are case-sensitive and it's often responsible for this type of problem.
Jeff, perfick solution as always!
Cheers
ChrisM

Only just got back to work after a rather long weekend. I just did this:

SELECT COUNT(*) FROM master.dbo.syscolumns

And it returned 4954, so my server is case sensitive, so will give the tally table another go making sure I double check the case on everything first.

I am so glad my server ain't screwed!

Steve