How to display array inside Ms Sql table for PowerBI?

Question

SQL Server

There are two tables in the Ms Sql database, one stores Id as an array in square brackets, separated by commas. The second one stores the values ​​of these Ids in Russian for a specific field.
How to display information in a human-readable form, with the ability to work with it in PowerBI? Or at least in SSMS.

Answer 1

DECLARE @ITEM VARCHAR(100) = '[ 3102, 3106]'
SELECT CAST( value AS INT ) AS ITEM 
  FROM STRING_SPLIT( SUBSTRING( @ITEM, 2, DATALENGTH( @ITEM ) - 2 ), ',')