Money conversion from idiotic, non-controlled data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Money conversion from idiotic, non-controlled data

Author

Topic

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-02-12 : 09:25:06

I live in a Dilbert cartoon. I just got done rewriting this crazy function I wrote to convert money. You wouldn't believe the things we get for money fields. And WE PAY them for it.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnGENMoneyFieldsConvert]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnGENMoneyFieldsConvert]
GO

CREATE FUNCTION dbo.fnGENMoneyFieldsConvert
--Name: fnGENMoneyFieldsConvert
--Purpose: This function formats money fields for conversion from LMS to LOS.
--
--Format: dbo.fnGENMoneyFieldsConvert
--Example_1: SELECT dbo.fnGENMoneyFieldsConvert('10 thou')
--Example_2: SELECT dbo.fnGENMoneyFieldsConvert('10000')
--Example_3: SELECT dbo.fnGENMoneyFieldsConvert('10 mil')
--Example_4: SELECT dbo.fnGENMoneyFieldsConvert('2 hun')
--Example_5: SELECT dbo.fnGENMoneyFieldsConvert('10 wouk')
--Example_6: SELECT dbo.fnGENMoneyFieldsConvert('10 kasdfa')
--Example_7: SELECT dbo.fnGENMoneyFieldsConvert('10 k')
--
--Action: Author: Date: Comments:
--------- ---------- --/--/---- ----------------------------------------
--Created Derrick Leggett 08/11/2003 Initial Development
--Modified Derrick Leggett 02/11/2004 Modified float to decimal to take into account
-- values over a million.
--
(
@inputText VARCHAR(255)
)
RETURNS MONEY
AS
BEGIN

--Declare needed values.
DECLARE
@prepText VARCHAR(255),
@outputText MONEY,
@i INT,
@char CHAR(1),
@leave BIT

--Perform a validity test on string to test for numeric and length.
IF @inputText = '' OR ISNUMERIC(@inputText) = 0
BEGIN
SELECT
@prepText = NULL,
@outputText = NULL
END

--Populate values to control the looping validation of text.
SELECT
@prepText = '',
@i = 1,
@leave = 0

--Loop through text to identify valid characters.
WHILE @i <= LEN(@inputText) AND @leave = 0
BEGIN
--Select the value of the string in position @i.
SELECT @char = (SELECT SUBSTRING(@inputText, @i, 1))

--Begin money value if value is 1-9. Does not allow small change or '0' as start.
IF ((@i = 1 OR @prepText = '') AND CHARINDEX(@char, '123456789') <> 0)
BEGIN
SET @prepText = @prepText + @char
END
ELSE
BEGIN
--Add next value to money string. Can be 0-9 or a decimal.
IF CHARINDEX(@char, '0123456789.') <> 0
BEGIN
SET @prepText = @prepText + @char
END
END

--If no more numerics exist, or if the value is small change only, exit string population.
IF (CHARINDEX(@char, '0123456789.') = 0) OR @prepText = '.'
BEGIN
SELECT @leave = 1
END

--Increment the string search to go to the next character.
SELECT @i = @i + 1
END

--Set values to NULL if the above result is non-numeric, blank, or >= 1 trillion.
IF @prepText = '' OR LEN(@prepText) > 9 OR ISNUMERIC(@prepText) = 0 OR @prepText = '.'
BEGIN
SELECT
@prepText = NULL,
@outputText = NULL
END
ELSE
BEGIN

--Update the input value to lower(@inputText) to level down comparison factor.
SELECT @inputText = LOWER(@inputText)

--Test for words like thousand in field, in addition to numerics.
IF @inputText LIKE '%thou%' AND @prepText IS NOT NULL
BEGIN
SELECT @prepText = CAST(@prepText AS DECIMAL(20,5)) * 1000
GOTO END_CALC
END

--Test for words like million in field, in addition to numerics.
IF @inputText LIKE '%mil%' AND @prepText IS NOT NULL
BEGIN
SELECT @prepText = CAST(@prepText AS DECIMAL(20,5)) * 1000000
GOTO END_CALC
END

--Test for words like hundred in field, in addition to numerics.
IF @inputText LIKE '%hun%' AND @prepText IS NOT NULL
BEGIN
SELECT @prepText = CAST(@prepText AS DECIMAL(20,5)) * 100
GOTO END_CALC
END

--Test for words like k in field, in addition to numerics.
IF @inputText LIKE '%k%' AND @prepText IS NOT NULL AND @inputText NOT LIKE '%[a-j]%' AND @inputText NOT LIKE '%[l-z]%'
BEGIN
SELECT @prepText = CAST(@prepText AS DECIMAL(20,5)) * 1000
GOTO END_CALC
END

END

END_CALC:

--Cast the decimal as money and return value.
SELECT @outputText = CAST(@prepText AS MONEY)

RETURN @outputText

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[fnGENMoneyFieldsConvert] TO [NovaGroup]
GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-02-12 : 19:10:34

I'm intrigued - LMS to LOS...maybe its just me, but I have no idea what that means...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-02-12 : 20:38:47

Shit man, a whole bunch of sympathy is coming your way, this is just plain EVIL!

Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources