0

a little just a continuation of my last topic. I have table:

id values
1 2;1|3;5|4;8|9;8|10;12
2 0;2|1;3|5;6
3 3;5|1;5|4;7|6;9

Column values is varchar and i need modify, that I get each number separately

I don't care if in new columns or as separate values in the same column.

id values
1 2
1 1
1 3
1 5
1 4
1 8
1 9
1 8
1 10
1 12

or

id values x1 y1 x2 y2 x3 y3 x4 y4 x5 y5
1 2;1|3;5|4;8|9;8|10;12 2 1 3 5 4 8 9 8 10 12

Both solutions would be great for me. i tried to achieve this at sql level and potentially using ssis:

SELECT values,
        SUBSTRING(values, 1, CHARINDEX(';', values)-1) AS x1,
        SUBSTRING(values,1,CHARINDEX(';', values)+1) AS y1
       /*CONVERT(float,STUFF(values,1,CHARINDEX(';',values),'')) AS y1
       CONVERT(float,LEFT(UserGuid,CHARINDEX(';',UserGuid)-1)) AS x1
      ,CONVERT(float,LEFT(UserGuid,CHARINDEX(';',UserGuid)-1)) AS x2,
       ,SUBSTRING(UserGuid, CHARINDEX(';', UserGuid) + 1, 1000) AS y2 */
FROM table

But I was told that this would be difficult to achieve at the sql level.

This adjustment may be necessary using some side scripting, any idea and suggestion will be great for me. Thank you

Cesc
  • 274
  • 2
  • 14
  • I suggest doing this cleanup work outside of SQL Server (e.g. using Java, PHP, Notepad++, etc.), then reimporting your data. This problem is not well handled by a database. – Tim Biegeleisen Jun 16 '21 at 06:18
  • @TimBiegeleisen and for example some python script would also go do you think? – Cesc Jun 16 '21 at 06:25
  • 1
    Take a look at https://stackoverflow.com/questions/67869048/is-there-any-way-to-loop-a-string-in-sql-server – hsn Jun 16 '21 at 06:40
  • @DaleK Sorry, I have already added my question. I'm more interested in an idea than a solution than a solution as such. So you don't get me wrong. – Cesc Jun 16 '21 at 09:06
  • @DaleK Of course. But when the problem, for example, cannot be solved in the way I intended. I have to change the solution, but the problem remains. In this case, I'm happy for the advice. – Cesc Jun 16 '21 at 09:23

1 Answers1

1

You can use STRING_SPLIT twice, this gets you all the values in rows:

SELECT t.id, s2.[value]
FROM Yourtable t
CROSS APPLY STRING_SPLIT(t.values, '|') s1
CROSS APPLY STRING_SPLIT(s1.[value], ';') s2
Charlieface
  • 52,284
  • 6
  • 19
  • 43