There is such a code in C #. How best to implement the same thing in SQL for MS SQL SERVER?

public static string CharToNumbers(string inputStr) { Dictionary<char, int> dict = new Dictionary<char, int> { {'A', 10}, {'B', 11}, {'C', 12}, {'D', 13}, {'E', 14}, {'F', 15}, {'G', 16}, {'H', 17}, {'I', 18}, {'J', 19}, {'K', 20}, {'L', 21}, {'M', 22}, {'N', 23}, {'O', 24}, {'P', 25}, {'Q', 26}, {'R', 27}, {'S', 28}, {'T', 29}, {'U', 30}, {'V', 31}, {'W', 32}, {'X', 33}, {'Y', 34}, {'Z', 35} }; for (int i = 0; i < inputStr.Length; i++) { int val = 0; if (dict.TryGetValue(inputStr[i], out val)) inputStr = inputStr.Replace(inputStr[i].ToString(), val.ToString()); } return inputStr; }

    2 answers 2

    Or you can use recursive query .

    for SQL Server :

     with tmp as (  select 1 as id  union all  select tmp.id+1  from tmp  where tmp.id < 26  )  select id, char(id + 64) character from tmp;

    For Oracle :

     select chr(level + 64) character, level + 10 id from dual connect by level <= 26;
    • And how can I screw this now for SQL Server? Suppose in the table BANK there is a column SCH_IK in which you need to make a replacement. How do I apply this to this column? - K. Kulahin
     CREATE OR REPLACE FUNCTION CharToNumbers( P_STR IN VARCHAR2) IS V_STR VARCHAR2(2*LENGTH(P_STR)); BEGIN FOR I IN 1..LENGTH(P_STR) LOOP V_STR := V_STR || CASE SUBSTR(P_STR, I, 1) WHEN 'A' THEN '10' WHEN 'B' THEN '11' ELSE '?' END END LOOP; RETURN V_STR; END;
    • And what will it look like for MS SQL SERVER? - K. Kulahin