December 12, 2008 at 8:48 pm
Hi all!
I have the following VB function which I am trying to convert to an SQL function but without much success. Could somebody give me a hand please?
Public Function ConvIn2Dec(ByVal strIn As String, ByRef inDetails As fractionDetails) As Decimal
'Set return value to 0
ConvIn2Dec = 0
inDetails.wholeNo = 0
inDetails.numerator = 0
inDetails.denominator = 0
'Check if characters in string is valid
If InStr(1, strIn, "/", vbTextCompare) = 0 Then
'No fraction is present so number is whole
ConvIn2Dec = Val(strIn)
Else
'Check for divider in wrong places
If Left(strIn, 1) = "/" Then Exit Function
If Right(strIn, 1) = "/" Then Exit Function
'Check characters
Dim valid As Boolean = True
Dim ind As Integer
For ind = 1 To Len(strIn)
If InStr(1, " .0123456789/", Mid(strIn, ind, 1), vbTextCompare) = 0 Then
valid = False
Exit For
End If
Next
'If invalid characters were found then exit
If valid = False Then Exit Function
'Check for no. of dividers
Dim divCount As Integer = 0
For ind = 1 To Len(strIn)
If Mid(strIn, ind, 1) = "/" Then
divCount = divCount + 1
End If
Next
'More than one divider was found
If divCount > 1 Then Exit Function
'Separate text from left & right of divider
Dim divPos As Integer = InStr(1, strIn, "/", vbTextCompare)
Dim strLeft As String = Left(strIn, divPos - 1)
Dim strRight As String = Right(strIn, Len(strIn) - divPos)
'Analyse left string
Dim spcPos As Integer = InStr(1, strLeft, " ", vbTextCompare)
Dim leftSpc As Integer
Dim rightspc As Integer
If spcPos = 0 Then
If Val(strLeft) <= 0 Or Val(strRight) <= 0 Then Exit Function
ConvIn2Dec = Val(strLeft) / Val(strRight)
inDetails.numerator = Val(strLeft)
inDetails.denominator = Val(strRight)
Else
leftSpc = Val(Left(strLeft, spcPos - 1))
rightspc = Val(Right(strLeft, Len(strLeft) - spcPos))
If leftSpc <= 0 Or rightspc <= 0 Then Exit Function
ConvIn2Dec = leftSpc + (rightspc / Val(strRight))
inDetails.wholeNo = leftSpc
inDetails.numerator = rightspc
inDetails.denominator = Val(strRight)
End If
End If
End Function
The above function, as you might have guessed serves me to convert a fractional string e.g. "5 3/16" in to a proper fraction "5.1875". In addition to the result, the function also gives a detailed breakdown of the string "5 3/16" into the structure fractionDetails which contains wholeNo, numerator and denominator, which in the case of the above example would be inDetails.wholeNo = 5, inDetails.numerator = 3, inDetails.denominator = 16.
I know it might be difficult to convert the above function completely to an SQL function, but could I at least have the result back, if not the fractionDetails structure?
Thanks!
December 12, 2008 at 11:13 pm
Here's a start:
Create Function ConvIn2Dec(@strIn As NVarchar(32)) Returns Numeric(18,9) As
Begin
Declare @ConvIn2Dec as Numeric(18,9)
--Set return value to 0
Select @ConvIn2Dec = 0
--Check if characters in @strIng is @valid
If CharIndex(@strIn, '/', 1) = 0
Begin
--No fraction is present so number is whole
Select @ConvIn2Dec = Cast(@strIn As Numeric(18,9))
End
Else
Begin
--Check for divider in wrong places
If Left(@strIn, 1) = '/' Return @ConvIn2Dec
If Right(@strIn, 1) = '/' Return @ConvIn2Dec
--Check characters
--If invalid characters were found then exit
If @strIn LIKE '%[^ .1234567890]/%' Return @ConvIn2Dec
--Check for no. of dividers
If @strIn LIKE '%/%/%' Return @ConvIn2Dec
--Separate text from left & right of divider
Declare @divPos Integer
Declare @strLeft NVarchar(32)
Declare @strRight NVarchar(32)
Declare @spcPos Integer
Select @divPos = CharIndex(@strIn, '/', 1)
, @strLeft = Left(@strIn, @divPos - 1)
, @strRight = Right(@strIn, Len(@strIn) - @divPos)
, @spcPos = CharIndex(@strLeft, ' ', 1)
--Analyse left @strIng
Declare @leftSpc As Integer
Declare @rightspc As Integer
If @spcPos = 0
Begin
If CAST(@strLeft as Numeric(18,9)) <= 0 Or Cast(@strRight as Numeric(18,9)) <= 0 Return NULL
Select @ConvIn2Dec = Cast(@strLeft as Numeric(18,9)) / Cast(@strRight as Numeric(18,9))
End
Else
Begin
Select @leftSpc = Cast(Left(@strLeft, @spcPos - 1) as Numeric(18,9))
, @rightspc = Cast(Right(@strLeft, Len(@strLeft) - @spcPos) as Numeric)
If @leftSpc <= 0 Or @rightspc <= 0 Return NULL
Select @ConvIn2Dec = @leftSpc + (@rightspc / Cast(@strRight as Numeric(18,9)))
End
End
Return @ConvIn2Dec
End
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 14, 2008 at 9:12 pm
thanks a lot for your post. I'm sure I'll be able to add the missing bits easily! Thanks again!
December 14, 2008 at 11:17 pm
wrt the missing bits: you should be aware that SQL Server places severe restrictions on what you can do in and with user defined functions. In particular, you cannot return results through anything other than the function return value. So this means that you can return secondary values through writable (or OUTPUT) parameters because they are not allowed for user defined functions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 15, 2008 at 6:58 am
ALTER FUNCTION dbo.fnResolveFractionals
(
@data VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
RETURNCASE
WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.0E * SUBSTRING(@data, CHARINDEX(' ', @data) + 1, CHARINDEX('/', @data) - CHARINDEX(' ', @data) - 1) / NULLIF(RIGHT(@data, LEN(@data) - CHARINDEX('/', @data)), 0)
WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 0 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 0 THEN CAST(@data AS FLOAT)
ELSE NULL
END
END
GO
DECLARE@Sample TABLE
(
data VARCHAR(20)
)
INSERT@Sample
SELECT'5 3/16' UNION ALL
SELECT'7' UNION ALL
SELECT'8 3' UNION ALL
SELECT'19 24/32' UNION ALL
SELECT'1024 784/32' UNION ALL
SELECT'8 3/0'
SELECT*,
dbo.fnResolveFractionals(data)
FROM@Sample
N 56°04'39.16"
E 12°55'05.25"
December 15, 2008 at 8:43 pm
Damned nice try, Peter... thought you had it, but not quite...
SET STATISTICS TIME ON
SELECT '5' , dbo.fnResolveFractionals('5') UNION ALL
SELECT '-5' , dbo.fnResolveFractionals('-5') UNION ALL
SELECT '3/16' , dbo.fnResolveFractionals('3/16') UNION ALL
SELECT '-3/16' , dbo.fnResolveFractionals('-3/16') UNION ALL
SELECT '5 3/16' , dbo.fnResolveFractionals('5 3/16') UNION ALL
SELECT '-5 3/16' , dbo.fnResolveFractionals('-5 3/16') UNION ALL
SELECT '3/' , dbo.fnResolveFractionals('3/') UNION ALL
SELECT '/16' , dbo.fnResolveFractionals('/16') UNION ALL
SELECT '-/16' , dbo.fnResolveFractionals('-/16') UNION ALL
SELECT '5/3/16' , dbo.fnResolveFractionals('5/3/16') UNION ALL
SELECT '6 5 3/16', dbo.fnResolveFractionals('6 5 3/16') UNION ALL
SELECT '5 3/0' , dbo.fnResolveFractionals('5 3/0') UNION ALL
SELECT '5-3/16' , dbo.fnResolveFractionals('5-3/16')
SET STATISTICS TIME OFF
[font="Courier New"]-------- ----------------------
5 5
-5 -5
3/16 NULL
-3/16 NULL
5 3/16 5.1875
-5 3/16 -4.8125
3/ NULL
/16 NULL
-/16 NULL
5/3/16 NULL
6 5 3/16 NULL
5 3/0 NULL
5-3/16 NULL
(13 row(s) affected)[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2008 at 8:54 pm
I thought this was a fun problem to solve and also gave the opportunity to demo both the power and readability of "Cascading CTE's".
Here's the function...
CREATE FUNCTION dbo.ConvertFractionToDecimal
/**********************************************************************************************************************
Purpose:
Converts a valid string representation of a positive or negative fraction to a DECIMAL(38,19) value. Returns NULL if
the string is not a valid representation of a positive or negative fraction.
Notes:
1. Leading/trailing spaces are allowed.
2. Multiple embedded spaces are not allowed. Only one space is allowed
Revision History:
Rev 00 - 15 Dec 2008 - Initial creation and test
REF - http://www.sqlservercentral.com/Forums/Topic619023-149-1.aspx
**********************************************************************************************************************/
--===== Declare the IO parameters
(@Fraction VARCHAR(20))
RETURNS DECIMAL(38,19)
AS
BEGIN ---------------------------------------------------------------------------------------------------------------
--===== Declare the return variable
DECLARE @DecimalValue DECIMAL(38,19)
--===== The following "cascading CTE's" solve the problem
;WITH
cteFindSign AS
(--==== Find the sign and strip it from the fraction removing any leading or trailing spaces
SELECT CASE
WHEN LEFT(LTRIM(RTRIM(@Fraction)),1) = '-'
THEN -1
ELSE 1
END AS MySign,
CASE
WHEN LEFT(@Fraction,1) IN ('+','-')
THEN LTRIM(RTRIM(SUBSTRING(@Fraction,2,20)))
ELSE LTRIM(RTRIM(@Fraction))
END AS MixedFraction
)
,
cteAddParts AS
(--==== Add any missing parts to make a Mixed Fraction format (even for improper fractions)
SELECT MySign,
CASE
WHEN MixedFraction NOT LIKE '%[0-9]/[0-9]%' --Add zero value fraction if missing
THEN MixedFraction + ' 0/1'
WHEN MixedFraction NOT LIKE '%[0-9] [0-9]%' --Add zero value whole number if missing
THEN '0 ' + MixedFraction
ELSE MixedFraction
END AS MixedFraction
FROM cteFindSign
)
,
cteReadyForParse AS
(--==== Do some format checking and get the MixedFraction ready for "dot" parsing
SELECT MySign,
MixedFraction = REPLACE(REPLACE(MixedFraction,' ','.'),'/','.')
FROM cteAddParts
WHERE MixedFraction NOT LIKE '%/%/%'
AND MixedFraction NOT LIKE '% % %'
AND MixedFraction NOT LIKE '%[^ 0-9/]%'
AND MixedFraction NOT LIKE '%/0%'
AND LEN(MixedFraction)-2 = LEN(REPLACE(REPLACE(MixedFraction,' ',''),'/',''))
)
--===== Do the parsing, the conversion, and add the SIGN back in to produce the correct decimal number
SELECT @DecimalValue =
(
PARSENAME(MixedFraction,3)
+(CAST(PARSENAME(MixedFraction,2) AS DECIMAL(38,19))/CAST(PARSENAME(MixedFraction,1) AS DECIMAL(38,19)))
)
* MySign
FROM cteReadyForParse
--===== Produce the return and exit
RETURN @DecimalValue
END ---------------------------------------------------------------------------------------------------------------
GO
Here's the test code... (feel free to add more tests)...
SET STATISTICS TIME ON
SELECT '5' , dbo.ConvertFractionToDecimal('5') UNION ALL
SELECT '-5' , dbo.ConvertFractionToDecimal('-5') UNION ALL
SELECT '3/16' , dbo.ConvertFractionToDecimal('3/16') UNION ALL
SELECT '-3/16' , dbo.ConvertFractionToDecimal('-3/16') UNION ALL
SELECT '5 3/16' , dbo.ConvertFractionToDecimal('5 3/16') UNION ALL
SELECT '-5 3/16' , dbo.ConvertFractionToDecimal('-5 3/16') UNION ALL
SELECT '3/' , dbo.ConvertFractionToDecimal('3/') UNION ALL
SELECT '/16' , dbo.ConvertFractionToDecimal('/16') UNION ALL
SELECT '-/16' , dbo.ConvertFractionToDecimal('-/16') UNION ALL
SELECT '5/3/16' , dbo.ConvertFractionToDecimal('5/3/16') UNION ALL
SELECT '6 5 3/16', dbo.ConvertFractionToDecimal('6 5 3/16') UNION ALL
SELECT '5 3/0' , dbo.ConvertFractionToDecimal('5 3/0') UNION ALL
SELECT '5-3/16' , dbo.ConvertFractionToDecimal('5-3/16') UNION ALL
SELECT '5A' , dbo.ConvertFractionToDecimal('5A') UNION ALL
SELECT 'A5' , dbo.ConvertFractionToDecimal('A5')
SET STATISTICS TIME OFF
And, here's the results...
[font="Courier New"]-------- ---------------------------------------
5 5.0000000000000000000
-5 -5.0000000000000000000
3/16 0.1875000000000000000
-3/16 -0.1875000000000000000
5 3/16 5.1875000000000000000
-5 3/16 -5.1875000000000000000
3/ NULL
/16 NULL
-/16 NULL
5/3/16 NULL
6 5 3/16 NULL
5 3/0 NULL
5-3/16 NULL
5A NULL
A5 NULL
(15 row(s) affected)[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2008 at 9:33 pm
terrific!
December 15, 2008 at 9:41 pm
Here's my final (?) function... Do you see any ways to improve it? It takes all sorts of inputs into consideration, even if you enter "a1/2"...
USE [Plyfoam(PR)]
GO
/****** Object: UserDefinedFunction [dbo].[ConvIn2Dec] Script Date: 12/16/2008 09:37:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ConvIn2Dec](@strIn nvarchar(10))
RETURNS DECIMAL(10,2)
BEGIN
DECLARE @result DECIMAL(10,2)
--SET @strIn = REPLACE(@strIn,SUBSTRING(@strIn,PATINDEX('%[^.1234567890]%',@strIn),1),'')
SELECT @result=0
--Check if characters in @strIng is @valid
If CharIndex('/',@strIn, 1) = 0
Begin
--No fraction is present so number is whole
Select @strIn=Replace(@strIn,Substring(@strIn, Patindex('%[^.1234567890]%',@strIn),1),'')
Select @result = Cast(@strIn AS DECIMAL(10,2))
End
Else
Begin
--Check for divider in wrong places
If Left(@strIn, 1) = '/' Return @result
If Right(@strIn, 1) = '/' Return @result
--Check characters
--If invalid characters were found then exit
If @strIn LIKE '%[^ .1234567890]/%' Return @result
--Check for no. of dividers
If @strIn LIKE '%/%/%' Return @result
--Separate text from left & right of divider
Declare @divPos Integer
Declare @strLeft NVarchar(32)
Declare @strRight NVarchar(32)
Declare @spcPos Integer
Select @divPos = CharIndex('/', @strIn, 1)
, @strLeft = Left(@strIn, @divPos - 1)
, @strRight = Right(@strIn, Len(@strIn) - @divPos)
, @spcPos = CharIndex(' ', @strLeft, 1)
--Analyse left @strIng
Declare @leftSpc As Integer
Declare @rightspc As Integer
If @spcPos = 0
Begin
If CAST(@strLeft as Numeric(18,9)) <= 0 Or Cast(@strRight as Numeric(18,9)) <= 0 Return NULL
Select @result = Cast(@strLeft as Numeric(18,9)) / Cast(@strRight as Numeric(18,9))
End
Else
Begin
Select @leftSpc = Cast(Left(@strLeft, @spcPos - 1) as Numeric(18,9))
, @rightspc = Cast(Right(@strLeft, Len(@strLeft) - @spcPos) as Numeric)
If @leftSpc <= 0 Or @rightspc <= 0 Return NULL
Select @result = @leftSpc + (@rightspc / Cast(@strRight as Numeric(18,9)))
End
End
Return @result
END
December 15, 2008 at 9:42 pm
Thanks for the feedback... the question now is do you really need the individual pieces?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2008 at 9:44 pm
Jedi Master (12/15/2008)
Here's my final (?) function... Do you see any ways to improve it? It takes all sorts of inputs into consideration, even if you enter "a1/2"...
Thought you didn't want it to take such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2008 at 9:48 pm
Well, I think so yes. I really cannot afford the function to return something other than a decimal value (no null or errors). So I really have to strip down the input and make sense out of it. If no sense can be made then a 0 value is returned...
December 15, 2008 at 9:50 pm
Jedi Master (12/15/2008)
Here's my final (?) function... Do you see any ways to improve it? It takes all sorts of inputs into consideration, even if you enter "a1/2"...
Simple test says that a bit of rework may be in order... 🙂
SET STATISTICS TIME ON
SELECT '5' , dbo.[ConvIn2Dec]('5') UNION ALL
SELECT '-5' , dbo.[ConvIn2Dec]('-5') UNION ALL
SELECT '3/16' , dbo.[ConvIn2Dec]('3/16') UNION ALL
SELECT '-3/16' , dbo.[ConvIn2Dec]('-3/16') UNION ALL
SELECT '5 3/16' , dbo.[ConvIn2Dec]('5 3/16') UNION ALL
SELECT '-5 3/16' , dbo.[ConvIn2Dec]('-5 3/16') UNION ALL
SELECT '3/' , dbo.[ConvIn2Dec]('3/') UNION ALL
SELECT '/16' , dbo.[ConvIn2Dec]('/16') UNION ALL
SELECT '-/16' , dbo.[ConvIn2Dec]('-/16') UNION ALL
SELECT '5/3/16' , dbo.[ConvIn2Dec]('5/3/16') UNION ALL
SELECT '6 5 3/16', dbo.[ConvIn2Dec]('6 5 3/16') UNION ALL
SELECT '5 3/0' , dbo.[ConvIn2Dec]('5 3/0') UNION ALL
SELECT '5-3/16' , dbo.[ConvIn2Dec]('5-3/16') UNION ALL
SELECT '5A' , dbo.[ConvIn2Dec]('5A') UNION ALL
SELECT 'A5' , dbo.[ConvIn2Dec]('A5')
SET STATISTICS TIME OFF
Results look like this...
[font="Courier New"]-------- ---------------------------------------
5 5.00
-5 5.00
3/16 0.19
-3/16 NULL
5 3/16 5.19
-5 3/16 NULL
3/ 0.00
/16 0.00
-/16 0.00
5/3/16 0.00
Msg 8114, Level 16, State 5, Line 2
Error converting data type nvarchar to numeric.[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2008 at 10:02 pm
I don't agree with returning a 0.00 for an invalid fraction, but it's not my code nor requirements. Replace the following section of code in my code and the invalids will magically convert from NULL to 0.000000000000...
--===== Produce the return and exit
RETURN ISNULL(@DecimalValue,0)
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2008 at 10:31 pm
Well yes as I have to make sure that any user input is correctly validated. I can't afford to have null or errors from the function so I need to get the actual answer or 0.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply