1 Table, 2 Column (int, varchar), 1 NEW Key as int

Question

Could use your help on this.

I have a table with 2 columns like this:

Column 1 |        Column 2
---------+--------------------
 1       | (         1,  1,1)
 2       | (        45,  2,9)
 3       | (      4456, 98,0)
 4       | (    196416,511,1)
97       | (   3213658, 45,7)
98       | (      2315,  6,7)
99       | (9999999999,999,9)

AS definition:

Column 1: max 2 characters
Column 2 - Part 1 max 10 characters
Column 2 - Part 2 max 3 characters
Column 2 - Part 3 max 1 characters

So the key has to be at least 16 characters long.

In the end I need help in creating the key for column 3 as int - looking like this:

Column 1 |        Column 2    |  Column 3 - Key
---------+--------------------+------------------
 1       | (         1,  1,1) | 0100000000010011
 2       | (        45,  2,9) | 0200000000450029
 3       | (      4456, 98,0) | 0300000044560980
 4       | (    196416,511,1) | 0400001964165111
97       | (   3213658, 45,7) | 9700032136580457
98       | (      2315,  6,7) | 9800000023150067
99       | (9999999999,999,9) | 9999999999999999

Would appreciate your help.

Thanks and Regards,

Erik

If you only need to update it just looks at the functions REPLICATE, LEN and SUBSTRING, with these three you should be able to form the third column — Joaquín, Jul 02 '20 at 17:27
You can use [`cast`](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15) to get `[Column 1]` as a string, [`replicate`](https://learn.microsoft.com/en-us/sql/t-sql/functions/replicate-transact-sql?view=sql-server-ver15) to build strings of zeroes, [`substring`](https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15) to pull pieces out of `[Column 2]`, [`right`](https://learn.microsoft.com/en-us/sql/t-sql/functions/right-transact-sql?view=sql-server-ver15) to get the zero-filled values. — HABO, Jul 02 '20 at 17:34
Start with `Right( Replicate( '0', 2 ) + Cast( [Column 1] as VarChar(2) ), 2 )` and go from there. — HABO, Jul 02 '20 at 17:38
The title says "NEW Key as int". If you really want an [`Int`] then you need to know that (1) it doesn't have leading zeroes and (b) the range is -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). A `BigInt` would address (b). A `Char(16)` takes care of both. — HABO, Jul 02 '20 at 17:56
Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Jul 02 '20 at 18:34
Hi Zohar, the delimited column can't be replaced. A tool from a service provider creates the table and the column this way. There is no way to have any influence on that. — Erik L., Jul 02 '20 at 19:47

Shnugo · Accepted Answer · 2020-07-03T07:55:05.940

You were told already, that this storage is NOT recommended. Rather use 3 separate columns for this and compute the needed value on the fly when needed.

As this seems to be padded correctly it might be as simple as this:

DECLARE @tbl TABLE(Column1 INT, Column2 VARCHAR(1000));
INSERT INTO @tbl VALUES   
 ( 1,'(         1,  1,1)')
,( 2,'(        45,  2,9)')
,( 3,'(      4456, 98,0)')
,( 4,'(    196416,511,1)')
,(97,'(   3213658, 45,7)')
,(98,'(      2315,  6,7)')
,(99,'(9999999999,999,9)');

SELECT *
      ,REPLACE(REPLACE(REPLACE(REPLACE(t.Column2,' ','0'),',',''),'(',''),')','')
FROM @tbl t;

Alternatively you can split this Column 2 and deal with the values separately:

--using the JSON-splitting here (needs v2016+)
--Below v2016 there are various splitting approaches (XML based, Jeff Moden's function and many more...)

SELECT t.*
      ,A.*
      ,CONCAT(REPLACE(STR(A.val1,10),' ','0')
             ,REPLACE(STR(A.val2, 3),' ','0')
             ,REPLACE(STR(A.val3, 1),' ','0')) AS [new key]
FROM @tbl t
CROSS APPLY OPENJSON(REPLACE(REPLACE(t.Column2,'(','[['),')',']]'))
            WITH(val1 BIGINT '$[0]'
                ,val2 BIGINT '$[1]'
                ,val3 BIGINT '$[2]') A;

The result

+---------+--------------------+------------+------+------+--------------------+
| Column1 | Column2            | val1       | val2 | val3 | new key            |
+---------+--------------------+------------+------+------+--------------------+
| 1       | (         1,  1,1) | 1          | 1    | 1    | 00000000010011     |
+---------+--------------------+------------+------+------+--------------------+
| 2       | (        45,  2,9) | 45         | 2    | 9    | 00000000450029     |
+---------+--------------------+------------+------+------+--------------------+
| 3       | (      4456, 98,0) | 4456       | 98   | 0    | 00000044560980     |
+---------+--------------------+------------+------+------+--------------------+
| 4       | (    196416,511,1) | 196416     | 511  | 1    | 00001964165111     |
+---------+--------------------+------------+------+------+--------------------+
| 97      | (   3213658, 45,7) | 3213658    | 45   | 7    | 00032136580457     |
+---------+--------------------+------------+------+------+--------------------+
| 98      | (      2315,  6,7) | 2315       | 6    | 7    | 00000023150067     |
+---------+--------------------+------------+------+------+--------------------+
| 99      | (9999999999,999,9) | 9999999999 | 999  | 9    | 99999999999999     |
+---------+--------------------+------------+------+------+--------------------+

update if below v2016

Before JSON one often used hack was something along this:

SELECT t.*
      ,A.*
      ,CONCAT(REPLACE(STR(A.AsXml.value('/x[1]','bigint'),10),' ','0')
             ,REPLACE(STR(A.AsXml.value('/x[2]','bigint'), 3),' ','0')
             ,REPLACE(STR(A.AsXml.value('/x[3]','bigint'), 1),' ','0')) AS [new key]
FROM @tbl t
CROSS APPLY(SELECT CAST(REPLACE(REPLACE(REPLACE(t.Column2,',','</x><x>'),'(','<x>'),')','</x>') AS XML)) A(AsXml);

Hint: In any case the result is a string and not a number. Otherwise you would not keep the leading zeros... But you might use CAST(... AS BIGINT) to achieve a key as (big) integer number.

1 Table, 2 Column (int, varchar), 1 NEW Key as int

1 Answers1