November 14, 2008 at 4:39 am
I am building an SQL 2000 function that will take a varchar and output a decimal.
The inputs and outputs should look like this:
Goboland Steel Gobo - returns 0.0
Procolor (39.0) - returns 39.0
Rosco J1 (43.?) (DHA Metal Gobos) - returns 43.0
Doughty 2, 3 & 4 (70.1) - returns 70.1
Clay Paky Spares (no PDF) - returns 0.0
DBX (A11.0) - returns 11.0
Le Mark (Stocked) (no PDF) - returns 0.0
Le Mark (non stocked) (no PDF) - returns 0.0
This is what I have done so far:
CREATE FUNCTION [dbo].[pbGroup] (@groupdesc varchar(50))
RETURNS varchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @value varchar(50)
-- Add the T-SQL statements to compute the return value here
SELECT @value = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(SUBSTRING(@groupdesc, CHARINDEX('(',
@groupdesc) + 1, 15), CHARINDEX(')', SUBSTRING(@groupdesc, CHARINDEX('(', @groupdesc) + 1,
15))), ')', ''), 'no PDF', '0'), 'A', ''), '?', '0'), 'Stocked', '0'), 'non stocked', '0')
-- Return the result of the function
RETURN @value
END
The problem with this (apart from it being ugly and convoluted) is that it creates blanks for the entries where there are no brackets, and when I try to cast the output as decimal I get an error about converting varchar to decimal.
When I view the results in enterprise manager these do not show as NULL, it just shows empty cells. I tried to do a replace nothing with 0 and it didn't work.
The correct output is achieved for all others, apart from they are strings being returned, not decimal, and the who point is to be able to use this field to sort records.
I am now stuck, and was wondering if anyone could suggest a better way to do this.
Cheers,
Steve
November 14, 2008 at 7:07 am
First, allow me to state that this isn't a good idea in the first place. Those numbers should be stripped out of that field and put in a new field... or at the very least, added to another field and left in the current one so that you don't have to try to pull them out every time you do anything. That said, here's another way of doing it using a Tally table. Not sure if Jeff is gonna slap me for this one, but we'll see. If you don't have a tally Table, read this article[/url] on how to create one, or simply do:
[font="Courier New"]SELECT TOP 20000 IDENTITY(INT,1,1) N
INTO Tally
FROM
MASTER..syscolumns s1, MASTER..syscolumns s2[/font]
[font="Courier New"]CREATE FUNCTION [dbo].[pbGroup] (@OrigString VARCHAR(200))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @NewString VARCHAR(200)
-- SELECT dbo.pbGroup('Doughty 2, 3 & 4 (70.1)')
-- SET @OrigString= 'Rosco J1 (43.?) (DHA Metal Gobos) '
SET @OrigString= REPLACE(@OrigString,'.?','.0')
SET @NewString = ''
-- Strip all unneeded characters out of string
SELECT @NewString = CASE WHEN SUBSTRING(@OrigString,N,1) IN ('0','1','2','3','4','5','6','7','8','9','.','(',')')
THEN @NewString + SUBSTRING(@OrigString,N,1)
ELSE @NewString
END
FROM Tally
WHERE N <= LEN(@OrigString)
-- Get rid of extra parens
SET @NewString = REPLACE(REPLACE(@NewString,'()',''),'( )','')
-- Grab the Number if Any
SET @NewString = SUBSTRING( @NewString,
CHARINDEX('(',@NewString,1)+1,
CHARINDEX(')',@NewString,1)-CHARINDEX('(',@NewString,1)-1)
-- Set to 0.0 if no number available
IF ISNULL(@NewString,'') LIKE '' SET @NewString = '0.0'
-- Return Value
RETURN @NewString
END
[/font]
November 14, 2008 at 7:13 am
Hi Seth,
Thank for a possible solution.
The data is in our sales system, its not something that can be changed. I am using the function in a view, this view is checked every 3 hours by a stored procedure, it is compared against a linked server (online) and then if there are any new rows it inserts them, and if any have changed then it inserts them, it also deletes any that aren't there any more.
Once the data is online there are no functions being used against it as the data is all in a table rather than a view. The local view just pulls all the relevant information that is needed into one place and then uploaded.
I do have a tally table so will give your method a go. My workaround was going to be making sure that all the descriptions had something in brackets, so there is always something left to change to 0.
Cheers,
Steve
November 14, 2008 at 7:44 am
Garadin, that's uncanny! I was working on this earlier and came up with the following:
CREATE FUNCTION [dbo].[uftGetNumbersInBrackets]
(
@String VARCHAR(50)
)
RETURNS DECIMAL (10,2)
AS
BEGIN
DECLARE @NewString VARCHAR(50)
SET @NewString = ''
SELECT @NewString = @NewString + SUBSTRING(@String, number,1)
FROM Numbers
WHERE number <= LEN(@String)
AND SUBSTRING(@String, number, 1) IN ('(', ')', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.')
AND number >= CHARINDEX('(',@String)
SET @NewString = REPLACE(REPLACE(@NewString, ')', ''), '(', '')
IF LEN(@NewString) > 0 RETURN CAST(@NewString AS DECIMAL (10,2))
RETURN 0
END
Cheers
ChrisM
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
November 14, 2008 at 7:53 am
Hah. Great minds think alike! (Although, I'm sure dumb people think alike too, so let's hope we fit the first category).
November 14, 2008 at 7:55 am
I prefer Chris's solution, it returns the decimal just like I needed 😀
November 14, 2008 at 8:01 am
Dooza (11/14/2008)
I prefer Chris's solution, it returns the decimal just like I needed 😀
Seth's is almost identical identical apart from the datatype of the return value. He did what you did (RETURNS varchar(50)), not what you said ("an SQL 2000 function that will take a varchar and output a decimal")
Cheers
ChrisM
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
November 14, 2008 at 8:06 am
Chris Morris (11/14/2008)
Dooza (11/14/2008)
I prefer Chris's solution, it returns the decimal just like I needed 😀Seth's is almost identical identical apart from the datatype of the return value. He did what you did (RETURNS varchar(50)), not what you said ("an SQL 2000 function that will take a varchar and output a decimal")
Ooops, my bad, sorry!
November 14, 2008 at 8:16 am
Yeah, I have a bad habit of "skimming" what people say and just looking at their code. Also, remember that these things will always be limited on what they can handle to all the different combinations of crap you can think of ahead of time. For example, both our scripts get this one wrong, although in different ways:
'Doughty 2, 3 & 4 (a2sdf) (70.1)'
I output 2, Chris outputs 270.1
Overall though, I agree, I think his solution is better as well, the extra SUBSTRING at the end of mine is a bit silly. TBH, if you don't need the extra SUBSTRING, then neither of us should even be including '(' or ')' in our list of characters, as we just strip them away again afterwards.
November 14, 2008 at 8:20 am
The key thing here for me is making sure the data is correct in the first place. Creating this function has brought to my attention all the variations and errors, so they are being fixed and procedures set so the data conforms to a set standard. This will limit the errors. For instance, the number should always be in the first set of brackets. This means Chris's solution is good for me.
Thank you all for your help!
November 14, 2008 at 8:24 am
Dooza (11/14/2008)
The key thing here for me is making sure the data is correct in the first place. Creating this function has brought to my attention all the variations and errors, so they are being fixed and procedures set so the data conforms to a set standard.
Absolutely. Glad we could help.
November 14, 2008 at 8:37 am
So what happens if there are several pairs of brackets, some with numbers in?
This picks the string between the first pair of brackets and grabs the numbers from it, but it could be changed to grab each bracketed string and return the first one containing numbers:
DECLARE @String VARCHAR(50)
SET @String = 'ABC (123.1 XYX) QTD (GFG) (55)'
DECLARE @NewString VARCHAR(50)
SET @NewString = ''
SELECT @NewString = @NewString + SUBSTRING(BitInBrackets, n.number,1)
FROM (
SELECT TOP 1 SUBSTRING(@String, number+1, CHARINDEX(')', @String, number) - (number+1)) AS BitInBrackets
FROM Numbers
WHERE number = CHARINDEX('(', @String, number)
ORDER BY number
) d, Numbers n
WHERE n.number <= LEN(BitInBrackets)
AND SUBSTRING(BitInBrackets, n.number, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.')
SELECT @NewString
Cheers
ChrisM
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
November 14, 2008 at 9:04 am
Garadin (11/14/2008)
Not sure if Jeff is gonna slap me for this one, but we'll see.
Heh... firing porkchop #1... [font="Arial Black"]SLAP![/font] 😛 Ya gotta put a clustered index or, better yet, a clustered PK on N to maintain the order of processing, Seth. The Fill Factor on that also helps to "pack" it into as few pages as possible for maximum speed. Last, but not least, the GRANT lets everyone use it so no worries on privs in the future...
--===== Create and populate the Tally table on the fly
SELECT TOP 20000
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
November 14, 2008 at 9:16 am
Jeff Moden (11/14/2008)
Garadin (11/14/2008)
Not sure if Jeff is gonna slap me for this one, but we'll see.Heh... firing porkchop #1... [font="Arial Black"]SLAP![/font] 😛 Ya gotta put a clustered index or, better yet, a clustered PK on N to maintain the order of processing, Seth. The Fill Factor on that also helps to "pack" it into as few pages as possible for maximum speed. Last, but not least, the GRANT lets everyone use it so no worries on privs in the future...
--===== Create and populate the Tally table on the flySELECT TOP 20000
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
Ack. Son of a... Yeah... on my real one I have it done correctly, I just wrote that script this off the top of my head, and apparently I shouldn't be doing that yet :blush:.
November 14, 2008 at 9:41 am
Heh... no problem. I hate typing the code off the top of my head so I keep the script handy at all times.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply