I am trying to work on a trigger which has a query as
Select Count(*)
from table name
For example, it's displaying count as 2. Is it possible to get count as two in English?
Once this trigger runs on a table, I would get email Notifications (database Email). There are .... records being inserted.
To be honest I was not sure if it's possible to display Count total in Alphabets.
I was trying something with ASCII, but no idea How it will work?
Here's a version of a function that takes a number and type of words to generate and returns a word string:
CREATE FUNCTION dbo.FN_NUMBER_AS_STRING(@pNum VARCHAR(30), @pCulture INT = 1)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @t TABLE (
culture INT
, level int
, frValue varchar(30) COLLATE database_default
, toValue varchar(30) COLLATE database_default
, name NVARCHAR(MAX) COLLATE database_default
, accumulatable BIT
)
DECLARE @name NVARCHAR(MAX)
, @from VARCHAR(30)
, @to VARCHAR(30)
, @numLower VARCHAR(30)
, @numHigher VARCHAR(30)
, @acc BIT
, @pNameHigher NVARCHAR(MAX)
, @pNameLower NVARCHAR(MAX)
, @level INT
;WITH lvl1 AS (
SELECT *
FROM (
VALUES (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight'), (9, 'nine')
, (10, 'ten'), (11, 'eleven'), (12, 'twelve'), (13, 'thirteen'), (14, 'fourteen'), (15, 'fifteen'), (16, 'sixteen'), (17, 'seventeen'), (18, 'eighteen'), (19, 'nineteen')
) x (val, name)
)
, lvl_rest AS (
SELECT *
FROM (
VALUES (20, 'twenty', 2), (30, 'thirty', 2), (40, 'fourty', 2), (50, 'fifty', 2), (60, 'sixty', 2), (70, 'seventy', 2), (80, 'eighty', 2), (90, 'ninety', 2)
, (100., 'hundred', 3), (1000., 'thousand', 4), (1000000., 'million', 5), (1000000000., 'billion', 6), (1000000000000., 'trillion', 7)
) x(val, name, level)
)
INSERT INTO @t (culture, level, frValue, toValue, name, accumulatable)
SELECT 1, LEN(t.val), CAST(t.val AS VARCHAR(30)), CAST(t.val AS VARCHAR(30)), t.name, 0
FROM lvl1 t
UNION ALL
SELECT 1, 2, CAST(t.val AS VARCHAR(30)), lead(CAST(t.val - 1 AS VARCHAR(30)), 1, CONCAT(t.val-1, t.val- 1)) OVER(ORDER BY t.val), t.name, CASE WHEN LEN(val) < 3 THEN 0 ELSE 1 END
FROM lvl_rest t;
-- Fixed values
INSERT INTO @t (culture, level, frValue, toValue, name, accumulatable)
SELECT 2, -1, x.frValue, x.toValue, x.name, 0
FROM (
VALUES (N'Few', 1, 4),(N'Several', 5, 9),(N'Pack', 10, 19),(N'Lots', 20, 49),(N'Horde', 50, 99),(N'Throng', 100, 249),(N'Swarm', 250, 499),(N'Zounds', 500, 999),(N'Legion', 1000, 9999999999999999)
) x (name,frValue, toValue)
-- figure out which level we're at
SELECT @level = CASE WHEN level < 0 OR @pNum = frValue THEN -1 ELSE 1 END
, @name = t.name
, @from = t.frValue
, @to = t.toValue
, @acc = t.accumulatable
FROM @t t
WHERE culture = @pCulture
AND RIGHT(REPLICATE('0', 30) + @pNum, 30) BETWEEN RIGHT(REPLICATE('0', 30) + frValue, 30) AND RIGHT(REPLICATE('0', 30) + toValue, 30)
IF @level < 0
BEGIN
RETURN(@name)
END
-- Get the lower part and the higher part of the number
SELECT @numHigher = CAST(FLOOR(CAST(@pNum AS NUMERIC(38, 0)) / CAST(@from AS NUMERIC(38, 0))) AS NUMERIC(38,0))
, @numLower = CAST(FLOOR(CAST(@pNum AS NUMERIC(38, 0)) % CAST(@from AS NUMERIC(38, 0))) AS NUMERIC(38,0))
--select @name, @pNum, @numHigher, @numLower
IF @acc = 1
SELECT @pNameHigher = dbo.FN_NUMBER_AS_STRING(@numHigher, @pCulture)
IF @numLower > 0
SELECT @pNameLower = dbo.FN_NUMBER_AS_STRING(@numLower, @pCulture)
RETURN(ISNULL(@pNameHigher+ ' ', '') + @name + ISNULL(' ' + @pNameLower, ''))
END
Testcode for english:
SELECT num, dbo.FN_NUMBER_AS_STRING(num, 1)
from (
values(1),(13), (29), (10000034)
) x (num)
Testcode for heroes:
SELECT num, dbo.FN_NUMBER_AS_STRING(num, 2)
from (
values(1),(13), (29), (203), (4333)
) x (num)
The function is pretty simple, it has a table of ranges and names for start of the range as well as some flags that controls how the range works.
A range is either accumulatable ie. one should take the lower parts separately (for example: 1234 consists of 1000 + 200 + 30 + 4, but 511 breaks down to 500 + 11; 11 is not accumulated by 10 + 1 since it has own name).
Finally, for every number, we find its range level and then recursively calculate the upper and lower parts.
For example:
123456 breaks down to following:
level= 1000, upper part= 123, lower part= 456.
123 breaks down to:
level= 100, upper part=1, lower part=23, generates 100
23 breaks down to:
level=20, upper part=1, lower part=3, generates 20
Both 1 and 3 are not breakable, so they are the end.
Finally one constructs the upper + level + lower part, which becomes: one hundred twenty three thousand. Similarly, 456 is broken down the same way.