sqlsql-servercount

Displaying count in SQL as alphabets instead of numbers


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?


Solution

  • 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.