我有一张这样的桌子
Value String ------------------- 1 Cleo, Smith
我想将逗号分隔的字符串分成两列
Value Name Surname ------------------- 1 Cleo Smith
我只需要两个固定的额外列
CREATE FUNCTION [dbo].[fn_split_string_to_column] ( @string NVARCHAR(MAX), @delimiter CHAR(1) ) RETURNS @out_put TABLE ( [column_id] INT IDENTITY(1, 1) NOT NULL, [value] NVARCHAR(MAX) ) AS BEGIN DECLARE @value NVARCHAR(MAX), @pos INT = 0, @len INT = 0 SET @string = CASE WHEN RIGHT(@string, 1) != @delimiter THEN @string + @delimiter ELSE @string END WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0 BEGIN SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos SET @value = SUBSTRING(@string, @pos, @len) INSERT INTO @out_put ([value]) SELECT LTRIM(RTRIM(@value)) AS [column] SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1 END RETURN END