Split string by two delimiters into two columns

user_1887466 0
Viewed 2059

I have a string value which has numeric values separated by comma and then by a pipe. I want to split them into a table with two columns. I could split the string by one delimiter but unfortunately couldn't find a way to split by two. Please help.

DECLARE @list NVARCHAR(MAX) = '1,101|2,202|3,303';

The result should be like below.

1   101
2   202
3   303

Thanks in advance.

sql-server sql
4 Answers

Try using this Table-valued Function, embed this SP to your main SP

ALTER FUNCTION [dbo].[delimiter]
(
@PARAM_IDS AS VARCHAR(MAX)
@PARAM_DELIMITER AS CHAR(1)
)
RETURNS 
@NEW_TABLE TABLE 
(
NUM INT NOT NULL IDENTITY,
ID INT NOT NULL
)
AS
BEGIN
DECLARE @NEXTSTRING AS NVARCHAR(MAX);
DECLARE @POS AS INT;
DECLARE @STRING AS NVARCHAR(MAX);
DECLARE @DELIMITER AS NVARCHAR(MAX);
SET @STRING = @PARAM_IDS;
SET @DELIMITER = @PARAM_DELIMITER;
SET @STRING = @STRING + @DELIMITER;
SET @POS = CHARINDEX(@DELIMITER,@STRING);

WHILE (@POS <> 0)
BEGIN
    SET @NEXTSTRING = SUBSTRING(@STRING,1,@POS - 1);
    INSERT @NEW_TABLE (ID) VALUES (@NEXTSTRING);
    SET @STRING = SUBSTRING(@STRING,@POS+1,len(@STRING));
    SET @POS = CHARINDEX(@DELIMITER,@STRING);
END 
    RETURN
END

then example of use

SET @DETAILS_COUNT = (SELECT COUNT(*) FROM delimiter(@PARAM_MS_UNIT_ID, @DELIMITER));
user_24645470
Related