Convert Polish Złoty (PLN) to text function

A number of Polish Złoty (PLN) to convert to text function.

Using:
SELECT [dbo].[fNumberToTextPol] (3213,22)

Result:



SQL Script:
create function [dbo].[fNumberToTextPol] (@num AS numeric(18,2), @isMoney as bit)
returns varchar(255)
AS

BEGIN
--converts numeric value to string
--handles up to 2 decimal places, with r
-- ounding
--if @isMoney = true then it will output
-- Zloty
--created by: serkan sonmez
--created on: 28/12/2008
-- declare @num numeric(18,2)
-- declare @isMoney bit
-- set @num = 1288.32
-- set @isMoney = 1

DECLARE @Split tinyint
DECLARE @iSplit tinyint
DECLARE @sNum varchar(20)
DECLARE @NumSet varchar(3)
DECLARE @Char char(3)
DECLARE @NumText varchar(255)
DECLARE @SetText varchar(100)
DECLARE @HunText varchar(100)
DECLARE @CharText varchar(50)
DECLARE @Match bit
DECLARE @Point tinyint
DECLARE @LEN_ tinyint
DECLARE @Cents varchar(2)
DECLARE @CentText varchar(100)
DECLARE @NumStrings TABLE (Num int, NumStr varchar(20))
INSERT INTO @NumStrings
SELECT 1, ' jeden'
UNION SELECT 2, ' dwa'
UNION SELECT 3, ' trzy'
UNION SELECT 4, ' cztery'
UNION SELECT 5, ' pięć'
UNION SELECT 6, ' sześć'
UNION SELECT 7, ' siedem'
UNION SELECT 8, ' osiem'
UNION SELECT 9, ' dziewięć'
UNION SELECT 10, ' dziesięć'
UNION SELECT 11, ' jedenaście'
UNION SELECT 12, ' dwanaście'
UNION SELECT 13, ' trzynaście'
UNION SELECT 14, ' czternaście'
UNION SELECT 15, ' piętnaście'
UNION SELECT 16, ' szesnaście'
UNION SELECT 17, ' siedemnaście'
UNION SELECT 18, ' osiemnaście'
UNION SELECT 19, ' dziewiętnaście'
UNION SELECT 20, ' dwadzieścia'
UNION SELECT 30, ' trzydzieści'
UNION SELECT 40, ' czterdzieści'
UNION SELECT 50, ' pięćdziesiąt'
UNION SELECT 60, ' sześćdziesiąt'
UNION SELECT 70, ' siedemdziesiąt'
UNION SELECT 80, ' osiemdziesiąt'
UNION SELECT 90, ' dziewięćdziesiąt'
UNION SELECT 100, ' sto'
UNION SELECT 200, ' dwieście'
UNION SELECT 300, ' Trzysta'
UNION SELECT 400, ' czterysta'
UNION SELECT 500, ' pięćset'
UNION SELECT 600, ' sześćset'
UNION SELECT 700, ' siedemset'
UNION SELECT 800, ' osiemset'
UNION SELECT 900, ' dziewięćset'

SET @sNum = cast(@num as varchar(20))
--convert any cent text first, then the
-- whole number
SET @Point = charindex('.', @sNum)
IF @Point > 0
BEGIN
SET @Cents = substring(@sNum, @Point + 1, 2)
SET @sNum = left(@sNum, @Point-1)
--if isMoney THEN combine the two digits (eg 11 = eleven)
IF @isMoney = 1
BEGIN --look FOR matches WITH the RIGHT two characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = @Cents)
IF @Match <> 0
BEGIN
SET @CentText = (select NumStr FROM @NumStrings WHERE Num = @Cents)
END
ELSE
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@Cents, 1))
END
END
ELSE --if NOT isMoney THEN treat each digit seperately (eg 11 = one one)
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1)),'') + isnull((select NumStr from @NumStrings where Num = right(@Cents, 1)),'')
END
END
SET @CentText = CASE LEN(@Cents) WHEN 1 THEN '/10' WHEN 2 THEN '/100' END
SET @CentText = @Cents + @CentText
--@CentText
IF @CentText IS NULL
SET @CentText = ''
--break the number into blocks of 3 char
-- acters
SET @Split = ((len(@sNum)-1) / 3) + 1
SET @iSplit = 0
SET @NumText = ''
WHILE @iSplit < @Split BEGIN SET @CharText = '' SET @HunText = '' SET @SetText = '' SET @NumSet = right(left(@sNum, len(@sNum) - @iSplit * 3), 3) IF len(@Numset) = 3 --Calculate ANY hundreds BEGIN SET @Char = left(@NumSet, 1) + '00' SET @HunText = isnull((select NumStr FROM @NumStrings WHERE Num = @Char) + '', '') SET @SetText = @HunText END --look FOR matches WITH the RIGHT two characters SET @Match = (select count(*) FROM @NumStrings WHERE Num = right(@NumSet, 2)) IF @Match <> 0
BEGIN
SET @CharText = (select NumStr FROM @NumStrings WHERE Num = right(@NumSet, 2))
END
ELSE
BEGIN
SET @CharText = isnull((select NumStr FROM @NumStrings WHERE Num = left(right(@NumSet, 2), 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@NumSet, 1))
END
--make sure there IS something in @CharText AND @SetText (ie IF @NumSet = x00, x000)
IF @CharText IS NULL SET @CharText = ''
IF @SetText IS NULL set @SetText = ''
--seperate ANY hundreds FROM tens/units WITH an 'and'
IF @HunText <> '' AND @CharText <> ''
SET @SetText = @SetText + ' '
--if there are no hundreds, it's the smallest SET AND there are other sets, ADD an 'and' before
IF @HunText = '' AND @CharText <> '' and @iSplit = 0 and @Split > 1 and @CentText = ''
--SET @SetText = ' and' + @SetText
SET @SetText = '' + @SetText
SET @SetText = @SetText + @CharText
--append the SET suffix text. ADD a ',' AS long as there IS something in the lowest set
IF @SetText <> ''
SET @SetText = @SetText + CASE @iSplit
WHEN 0 THEN ''
WHEN 1 THEN ' tysiąc'
WHEN 2 THEN ' milion'
WHEN 3 THEN ' milionów'
WHEN 4 THEN ' trillion'
WHEN 5 THEN ' quadrillion'
END + CASE WHEN len(@NumText) > 0 THEN ',' ELSE '' end
SET @NumText = @SetText + @NumText
SET @iSplit = @iSplit + 1
END
--add any dollars and cent text
IF @isMoney = 1 AND @NumText <> '' and rtrim(ltrim(@NumText)) <> 'jeden'
--SET @NumText = @NumText + ' Dollars'
SET @NumText = @NumText + ' PLN'
IF @isMoney = 1 AND rtrim(ltrim(@NumText)) = 'jeden'
--SET @NumText = @NumText + ' Dollar'
SET @NumText = @NumText + ' PLN'
IF @isMoney = 1 AND @CentText <> '' and rtrim(ltrim(@CentText)) <> 'jeden'
SET @CentText = @CentText + ''
IF @isMoney = 1 AND rtrim(ltrim(@CentText)) = 'jeden'
SET @CentText = @CentText + ''
IF @isMoney = 0 AND @CentText <> ''
SET @CentText = ' Point' + @CentText
IF @isMoney = 1 AND @NumText <> '' and @CentText <> ''
--SET @CentText = ' and' + @CentText
SET @CentText = ' ' + @CentText
--combine dollars and cents
SET @NumText = @NumText + @CentText
--add 'Minus' for negative numbers
IF left(@sNum, 1) = '-'
SET @NumText = 'Minus' + @NumText
RETURN @NumText
END

Hiç yorum yok:

BlackListIP control on Serenity platform (.NET Core)

 In the Serenity platform, if you want to block IPs that belong to people you do not want to come from outside in the .net core web project,...