Display letters instead of string numbers in SQL-CodePudding

How can I write a query that prints in response to letters by giving row ID and size number?

Example:

ID = 4000

NAME = 38/39/40/41/42/43/44

My Input :

ID = 4000

NAME = 40

Result = A/B/C/D/E/F/G 40 = C

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl AS TABLE (ID INT PRIMARY KEY, tokens VARCHAR(MAX));
INSERT INTO @tbl (ID, tokens) VALUES
(2000, '44/46/48/50/52/54'),
(4000, '38/39/40/41/42/43/44');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '/'
    , @alphabet VARCHAR(50) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    , @ID INT = 4000
    , @token VARCHAR(20) = '40';

SELECT *
    , Result = IIF(pos > 0, SUBSTRING(@alphabet,pos,1),NULL)
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r>'   
    REPLACE(tokens, @separator, '</r><r>')   
    '</r></root>' AS XML)
    .query('
        for $x in /root/r
        let $pos := count(/root/r[. << $x[1]])   1
        return if ($x/text()=sql:variable("@token")) then $pos
        else ()
    ').value('.', 'INT')) AS t1(pos)
WHERE ID = @ID;

Output

 ------ ---------------------- ----- -------- 
|  ID  |        tokens        | pos | Result |
 ------ ---------------------- ----- -------- 
| 4000 | 38/39/40/41/42/43/44 |   3 | C      |
 ------ ---------------------- ----- --------