Converting 152.50 to One Hundred Fifty Two and 50/100 4

[dariopowell]

I am hoping to find a SQL function that will allow me to cast a money/int/dec field into actual text. So far I have had no luck.
Ex:
$152.50
needs to become

One Hundred Fifty Two and 50/100

As Im sure you have guessed, this is needed for printing checks.
Before I delve into the endless case..when..where SQL statements, I thought I would ask here first.

Thnaks in advance for your help!!

 

[SQLSister]

Hmmm an interesting problem.

Ok first I would create a table to help me with this that matches numbers to the text you want. three columns - one for the digit (0-9) and one for what to use for the ones, one hundres, one thousands etc. place and one for what to use for the tens, ten thousands, etc.


Then I would use the substring command to pull each item of the number out and match it to it's proper value in the table you created and build a string that way. Don;t forget to substitute and for the periods and add spaces as appropriate. If you always have 2 digits after the decimal, then you can use the right function to grab them and convert to the X/100 part.

digits like 19 will need special handling as the ones digit comes before the expressionfor the teens. So you will probably need a case statment for that part.

Questions about posting. See faq183-874

 

[RobertT687]

Here's a link to a Knowledgebase article for access that does what you want. You may be able to adapt it to SQL.

http://support.microsoft.com/default.aspx?scid=kb;en-us;234302

 

[Madawc]

Do you know about Crystal Reports? It is set up to do this sort of thing very easily, and is built around SQL data.

Madawc Williams (East Anglia, UK)

 

[dbomrrsm]

is this of any help:

--Name: Convert money value to the word
--      value.
-- Description:With this script you can 
--     supply an money value such as $12525.83 
--     and it will return the word value "Twelv
--     e Thousand Five Hundred And Twenty-Five 
--     Dollars And Eighty-Three Cents".
It IS comprised OF a TABLE FOR Number TO Name AND a PROCEDURE to parse INTO its pieces
NOTE: This ONLY goes TO billions at this point, but easy enough to expand. 
May WORK even better AS a function IN 2000. 

--     

--You will need this table.--
CREATE TABLE NumNameTbl (
	[val] [int] NOT NULL,
	[numname] [varchar](20) NOT NULL,
	CONSTRAINT [PK_NumNameTbl_val] PRIMARY KEY CLUSTERED 
	(
		[val]
	) WITH FILLFACTOR = 100
)
GO 
--Insert the value records, yes this is 
--     a heap table.--
INSERT INTO NumNameTbl (val,numname) VALUES (1,'One')
INSERT INTO NumNameTbl (val,numname) VALUES (2,'Two')
INSERT INTO NumNameTbl (val,numname) VALUES (3,'Three')
INSERT INTO NumNameTbl (val,numname) VALUES (4,'Four')
INSERT INTO NumNameTbl (val,numname) VALUES (5,'Five')
INSERT INTO NumNameTbl (val,numname) VALUES (6,'Six')
INSERT INTO NumNameTbl (val,numname) VALUES (7,'Seven')
INSERT INTO NumNameTbl (val,numname) VALUES (8,'Eight')
INSERT INTO NumNameTbl (val,numname) VALUES (9,'Nine')
INSERT INTO NumNameTbl (val,numname) VALUES (10,'Ten')
INSERT INTO NumNameTbl (val,numname) VALUES (11,'Eleven')
INSERT INTO NumNameTbl (val,numname) VALUES (12,'Twelve')
INSERT INTO NumNameTbl (val,numname) VALUES (13,'Thirteen')
INSERT INTO NumNameTbl (val,numname) VALUES (14,'Fourteen')
INSERT INTO NumNameTbl (val,numname) VALUES (15,'Fifteen')
INSERT INTO NumNameTbl (val,numname) VALUES (16,'Sixteen')
INSERT INTO NumNameTbl (val,numname) VALUES (17,'Seventeen')
INSERT INTO NumNameTbl (val,numname) VALUES (18,'Eightteen')
INSERT INTO NumNameTbl (val,numname) VALUES (19,'Nineteen')
INSERT INTO NumNameTbl (val,numname) VALUES (20,'Twenty')
INSERT INTO NumNameTbl (val,numname) VALUES (30,'Thirty')
INSERT INTO NumNameTbl (val,numname) VALUES (40,'Fourty')
INSERT INTO NumNameTbl (val,numname) VALUES (50,'Fifty')
INSERT INTO NumNameTbl (val,numname) VALUES (60,'Sixty')
INSERT INTO NumNameTbl (val,numname) VALUES (70,'Seventy')
INSERT INTO NumNameTbl (val,numname) VALUES (80,'Eighty')
INSERT INTO NumNameTbl (val,numname) VALUES (90,'Ninty')
GO
/* The procedure. */
CREATE PROCEDURE ip_ReturnMoneyName
	@Money Money
AS
SET NOCOUNT ON
DECLARE @MonName VARCHAR(8000)
DECLARE @MonStr VARCHAR(100)
DECLARE @TempMon VARCHAR(3)
SET @MonStr = CAST(@Money AS VARCHAR(100))
SET @MonName = ''
IF CAST(RIGHT(@MonStr,2) AS INT) > 0


    BEGIN
    	SET @TempMon = RIGHT(@MonStr,2)
    	SET @MonName = 'Pence'
    	SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(@TempMon,1) WHEN 1 THEN @TempMon ELSE RIGHT(@TempMon,1) END)
    	
    	IF LEFT(@TempMon,1) NOT IN (0,1)
    		SELECT @MonName = numname + (CASE LTRIM(@MonName) WHEN 'PENCE' THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(@TempMon,1) + '0') AS int)
    	IF LEN(@MonStr) = 4 AND LEFT(@MonStr,1) = 0
    	BEGIN
    		SET @MonName = @MonName
    	END
    	ELSE
    		SET @MonName = 'And ' + @MonName
END
SET @MonStr = LEFT(@MonStr,LEN(@MonStr) - 3)
/* Ones, Tens Hundreds */
IF LEN(@MonStr) > 0


    BEGIN
    	SET @TempMon = RIGHT(@MonStr,3)
    	SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon)) 
    	IF CAST(@TempMon AS INT) != 0
    	BEGIN
    		SET @MonName = 'Pounds ' + @MonName
    		SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
    		IF LEN(@TempMon) > 1
    		BEGIN
    			IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
    				SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
    		END
    		
    		IF CHARINDEX('Pounds',@MonName) > 1 AND (LEN(@TempMon) = 3 OR LEN(@MonStr) > 0)
    			IF CHARINDEX(' AND ',@MonName) = 0
    				SET @MonName = 'And ' + @MonName
    		IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
    			SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
    	END
END
/* Thousands */
IF LEN(@MonStr) > 0


    BEGIN
    	SET @TempMon = RIGHT(@MonStr,3)
    	SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon)) 
    	IF CAST(@TempMon AS INT) != 0
    	BEGIN
    		IF CHARINDEX('Pounds',@MonName) = 0
    			SET @MonName = 'Pounds ' + @MonName
    		SET @MonName = 'Thousand ' + @MonName
    		SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
    		IF LEN(@TempMon) > 1
    		BEGIN
    			IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
    				SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
    		END
    		IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
    			SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
    	END
END
/* Million */
IF LEN(@MonStr) > 0


    BEGIN
    	SET @TempMon = RIGHT(@MonStr,3)
    	SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon)) 
    	IF CAST(@TempMon AS INT) != 0
    	BEGIN
    		IF CHARINDEX('Pounds',@MonName) = 0
    			SET @MonName = 'Pounds ' + @MonName
    		SET @MonName = 'Million ' + @MonName
    		SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
    		IF LEN(@TempMon) > 1
    		BEGIN
    			IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
    				SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
    		END
    		IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
    			SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
    	END
END
/* Billion */
IF LEN(@MonStr) > 0


    BEGIN
    	SET @TempMon = RIGHT(@MonStr,3)
    	SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon)) 
    	IF CAST(@TempMon AS INT) != 0
    	BEGIN
    		IF CHARINDEX('Pounds',@MonName) = 0
    			SET @MonName = 'Pounds ' + @MonName
    		SET @MonName = 'Billion ' + @MonName
    		SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
    		IF LEN(@TempMon) > 1
    		BEGIN
    			IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
    				SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
    		END
    		IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
    			SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
    	END
END
IF CHARINDEX('One',@MonName) = 1 AND CHARINDEX('Pounds',@MonName) = 5
	SET @MonName = REPLACE(@MonName,'Pounds','Pounds')
SELECT @MonName AS MoneyName
GO


--Example - this is the highest value it can accept

exec ip_ReturnMoneyName 999999999999.99

[blue]DBomrrsm[/blue]

 

[dariopowell]

Thanks for all your replies. I wound up writing a function to handle this.