Search code examples
sqlsql-server-2016

how convert string in column to object json in same column sql server

I want to convert column ExtId to Object json and update table where ExtId is not null.

Is it possible to get a sample script?

**Table Before**
| Id  | brand    |ExtId|
| --- | ---      | --- |
| 1   | volvo    | 1040|
| 2   | bmw      |     |
| 3   | Mercedes | 1501|
| 4   | Mazda    |     |
| 5   | Fiat     | 132 |

**Table After**
| Id  | brand    |      ExtId        |
| --- | ---      |       ---         | 
| 1   | volvo    | {"user" : "1040" }|
| 2   | bmw      |                   |
| 3   | Mercedes | {"user" : "1501" }|
| 4   | Mazda    |                   |
| 5   | Fiat     | {"user" : "132" } |

thank you in advance :)

Solution

  • you can easily achieve this without using any specioul function:

    update tableName set extid= '{"user" : "' +EXTID+'"}' where extid is not null