March 16, 2021 at 4:08 pm
DECLARE @AMOUNT AS MONEY =1254125.65
DECLARE @STR_AMOUNT AS VARCHAR(MAX) =CONVERT(VARCHAR(MAX), @AMOUNT);
DECLARE @PREFIX AS VARCHAR(MAX)= ''
DECLARE @SUFIX AS VARCHAR(MAX)=''
DECLARE @NUM_WORD AS VARCHAR(MAX)=''
DECLARE @NUM_WORD_DECIMALS AS VARCHAR(MAX)=''
SELECT @PREFIX= SUBSTRING(@STR_AMOUNT,1, IIF(@STR_AMOUNT LIKE '%.%', PATINDEX('%.%',@STR_AMOUNT)-1,LEN(@STR_AMOUNT)) )
SELECT @SUFIX= SUBSTRING(@STR_AMOUNT,IIF(@STR_AMOUNT LIKE '%.%', PATINDEX('%.%',@STR_AMOUNT)+1,LEN(@STR_AMOUNT)),LEN(@STR_AMOUNT))
;WITH NUM_WRD AS
(
SELECT 1 AS NUM, 'ONE' AS WRD UNION SELECT 2,'TWO' UNION SELECT 3,'THREE' UNION SELECT 4,'FOUR' UNION SELECT 5,'FIVE' UNION SELECT 6,'SIX' UNION SELECT 7,'SEVEN' UNION SELECT 8,'EIGHT' UNION SELECT 9,'NINE' UNION SELECT 0,''
UNION ALL
SELECT 11 AS NUM, 'ELEVEN' AS WRD UNION SELECT 12,'TWELVE' UNION SELECT 13,'THIRTEEN' UNION SELECT 14,'FOURTEEN' UNION SELECT 15,'FIFTEEN' UNION SELECT 16,'SIXTEEN' UNION SELECT 17,'SEVENTEEN' UNION SELECT 18,'EIGHTEEN' UNION SELECT 19,'NINTEEN'
UNION ALL
SELECT 10 AS NUM, 'TEN' AS WRD UNION SELECT 20,'TWENTY' UNION SELECT 30,'THIRTY' UNION SELECT 40,'FOURTY' UNION SELECT 50,'FIFTY' UNION SELECT 60,'SIXTY' UNION SELECT 70,'SEVENTY' UNION SELECT 80,'EIGHTY' UNION SELECT 90,'NINETY'
UNION ALL
SELECT 100 AS NUM, 'HUNDRED' AS WRD UNION SELECT 1000,'THOUSAND' UNION SELECT 1000000,'MILLION' UNION SELECT 100000000,'BILLION' UNION SELECT 1000000000000,'TRILLION'
),
NM_PREFIX AS
(
SELECT 1 AS LVL,LEN(@PREFIX)-1 AS DIG, CAST(SUBSTRING(@PREFIX,1,1) AS INT) AS CHR, CAST( '1'+REPLICATE('0',LEN(@PREFIX)-1) AS BIGINT ) AS NUM, (LEN(@PREFIX))%3 AS MD
UNION ALL
SELECT LVL+1 AS LVL,DIG-1 AS DIG, CAST(SUBSTRING(@PREFIX,LVL+1,1) AS INT) AS CHR,CAST( '1'+REPLICATE('0',DIG-1) AS BIGINT ) AS NUM,DIG%3 AS MD FROM NM_PREFIX WHERE LEN(@PREFIX) >LVL
),
NM_SUFFIX AS
(
SELECT 1 AS LVL,LEN(@SUFIX)-1 AS DIG, CAST(SUBSTRING(@SUFIX,1,1) AS INT) AS CHR, CAST( '1'+REPLICATE('0',LEN(@SUFIX)-1) AS BIGINT ) AS NUM, (LEN(@SUFIX))%3 AS MD
UNION ALL
SELECT LVL+1 AS LVL,DIG-1 AS DIG, CAST(SUBSTRING(@SUFIX,LVL+1,1) AS INT) AS CHR,CAST( '1'+REPLICATE('0',DIG-1) AS BIGINT ) AS NUM,DIG%3 AS MD FROM NM_SUFFIX WHERE LEN(@SUFIX) >LVL
),
NUM_PREFIX_CON AS
(
SELECT x.*,(SELECT TOP 1 WRD FROM NUM_WRD WHERE x.ACT_NUM=NUM) AS ACT_WRD FROM (
SELECT x.*,z.WRD AS WRD_TEN,
CASE WHEN x.NUM= 10 THEN NULL WHEN x.NUM=1 THEN '' WHEN MD=0 THEN 'HUNDRED' ELSE y.WRD END AS WRD_HUN,
IIF((y.WRD IS NULL AND MD!=0) OR x.NUM=10, CHR*10, CHR) AS ACT_NUM FROM NM_PREFIX x INNER JOIN NUM_WRD z ON z.NUM=CHR LEFT JOIN NUM_WRD y ON y.NUM=x.NUM
) x
),
NM_SUFFIX_CON AS
(
SELECT x.*,(SELECT TOP 1 WRD FROM NUM_WRD WHERE x.ACT_NUM=NUM) AS ACT_WRD FROM (
SELECT x.*,z.WRD AS WRD_TEN,
CASE WHEN x.NUM= 10 THEN NULL WHEN x.NUM=1 THEN '' WHEN MD=0 THEN 'HUNDRED' ELSE y.WRD END AS WRD_HUN,
IIF((y.WRD IS NULL AND MD!=0) OR x.NUM=10, CHR*10, CHR) AS ACT_NUM FROM NM_SUFFIX x INNER JOIN NUM_WRD z ON z.NUM=CHR LEFT JOIN NUM_WRD y ON y.NUM=x.NUM
) x
),
NUM_WROD_DECIMALS AS
(
SELECT 0 AS K, * FROM NUM_PREFIX_CON
UNION ALL
SELECT 1 AS K, * FROM NM_SUFFIX_CON
)
SELECT
@NUM_WORD=COALESCE(@NUM_WORD + ' ', '') + IIF(K=0, IIF(WRD_HUN IS NULL,'AND ' ,'') + '' + IIF(WRD_HUN IS NULL, ISNULL(ACT_WRD,'') ,ISNULL(WRD_TEN,'')) +' ' +ISNULL(WRD_HUN,''),''),
@NUM_WORD_DECIMALS=COALESCE(@NUM_WORD_DECIMALS + ' ', '') + IIF(K=1, IIF(WRD_HUN IS NULL,'AND ' ,'') + '' + IIF(WRD_HUN IS NULL, ISNULL(ACT_WRD,'') ,ISNULL(WRD_TEN,'')) +' ' +ISNULL(WRD_HUN,''),'')
FROM NUM_WROD_DECIMALS --ORDER BY 1
SELECT REPLACE(LTRIM(@NUM_WORD) + LTRIM(@NUM_WORD_DECIMALS),' ',' ')
December 29, 2022 at 12:02 pm
This was removed by the editor as SPAM
December 29, 2022 at 12:20 pm
This was removed by the editor as SPAM
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply