Given: contact information. Places in the site field instead of one value with several delimiters of type | sometimes I would like to "save" this information by creating a line that completely repeats all the fields of the parent line except the "site" field, which will be different in the child lines. How it looks in the table

The String Split function does not work, it is from the 2016 version.

From the found there is such an option:

Declare @products varchar(200) = '1|20|3|343|44|6|8765' Declare @individual varchar(20) = null WHILE LEN(@products) > 0 BEGIN IF PATINDEX('%|%', @products) > 0 BEGIN SET @individual = SUBSTRING(@products, 0, PATINDEX('%|%', @products)) SELECT @individual SET @products = SUBSTRING(@products, LEN(@individual + '|') + 1, LEN(@products)) END ELSE BEGIN SET @individual = @products SET @products = NULL SELECT @individual END END

1 answer 1

It is not clear what you need. If splitstring, then there is such an implementation:

 --разбивает строку на таблицу --select * from dbo.fnSplitString('1|20|3|343|44|6|8765','|') ALTER FUNCTION [dbo].[fnSplitString] ( @source VARCHAR(MAX), @delimiter varchar(1)) RETURNS @returnList TABLE (item varchar (max), ind int) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT DECLARE @index INT = 0 WHILE CHARINDEX(@delimiter, @source) > 0 BEGIN SELECT @pos = CHARINDEX(@delimiter, @source) SELECT @name = SUBSTRING(@source, 1, @pos-1) INSERT INTO @returnList SELECT @name,@index SELECT @source = SUBSTRING(@source, @pos+1, LEN(@source)-@pos) set @index=@index+1 END INSERT INTO @returnList SELECT @source,@index RETURN END
  • Great thing, thanks. - Mar'ya Mazaeva