Select from string?

Question

SQL

Good afternoon. There is a text type column in the table, it contains data sets with ";" delimiter.
example:
columns_with_some_data line1;line2;line3;
Tell me how to use SQL to make 3 lines from the line above:
columns_with_some_data line1;
columns_with_some_data line2;
columns_with_some_data line3;
Thank you!
forgot to specify. All this revolves on MS sql

Answer 1

For 2012 we use:

CREATE FUNCTION [dbo].[CalcHelperString_Split]
(   
    @str VARCHAR(MAX),
    @delimiter VARCHAR(10)
)
RETURNS TABLE 
AS
RETURN 
  SELECT t.c.value('(./text())[1]', 'VARCHAR(MAX)') as item
  FROM 
  ( 
    SELECT x = CONVERT(XML, '<i>' + REPLACE(@str, @delimiter, '</i><i>') + '</i>').query('.')
  ) a
  CROSS APPLY x.nodes('i') t(c)
  WHERE t.c.value('(./text())[1]', 'VARCHAR(MAX)') is not null;
GO

Found on StackOverflow. Works the fastest we could find.
Therefore, regarding your request, you can do this:

DECLARE @TTT TABLE (column1 VARCHAR(50), column2 VARCHAR(100))
 
INSERT INTO @TTT (column1, column2)
    VALUES('Какие-то данные 1', 'строка1;строка2;строка3;'),
        ('Какие-то данные 2', 'строка4;строка5;строка6;'),
        ('Какие-то данные 3', 'строка7;'),
        ('Какие-то данные 4', 'строка8;строка9;')

SELECT * FROM @TTT t
CROSS APPLY dbo.CalcHelperString_Split(t.column2, ';') f

Answer 2

Should help
STRING_SPLIT (Transact-SQL)

Answer 3

string functions for ms sql