You need to create a table valued function to split the string first and then cross apply it with the main table.
Please see the following script for quick testing:
CREATE FUNCTION [fnDStringToTable] (@list NVARCHAR(4000), @delimiter NCHAR(1) = ',' ) RETURNS @tableList TABLE( value int) AS BEGIN DECLARE @value int DECLARE @position INT
SET @list = LTRIM(RTRIM(@list))+ ',' SET @position = CHARINDEX(@delimiter, @list, 1)
IF REPLACE(@list, @delimiter, '') <> '' BEGIN WHILE @position > 0 BEGIN SET @value = convert(int,LTRIM(RTRIM(LEFT(@list, @position - 1)))) IF @value <> '' BEGIN INSERT INTO @tableList (value) VALUES (@value) END SET @list = RIGHT(@list, LEN(@list) - @position) SET @position = CHARINDEX(@delimiter, @list, 1)
Hi,
ReplyDeleteYou need to create a table valued function to split the string first and then cross apply it with the main table.
Please see the following script for quick testing:
CREATE FUNCTION [fnDStringToTable] (@list NVARCHAR(4000), @delimiter NCHAR(1) = ',' )
RETURNS
@tableList TABLE( value int) AS
BEGIN
DECLARE @value int
DECLARE @position INT
SET @list = LTRIM(RTRIM(@list))+ ','
SET @position = CHARINDEX(@delimiter, @list, 1)
IF REPLACE(@list, @delimiter, '') <> ''
BEGIN
WHILE @position > 0
BEGIN
SET @value = convert(int,LTRIM(RTRIM(LEFT(@list, @position - 1))))
IF @value <> ''
BEGIN
INSERT INTO @tableList (value)
VALUES (@value)
END
SET @list = RIGHT(@list, LEN(@list) - @position)
SET @position = CHARINDEX(@delimiter, @list, 1)
END
END
RETURN
END
create table testa (cola varchar(10), metricset varchar(10), extname varchar(10))
INSERT INTO TESTA VALUES ( 'CONF1','1,2,3','E1');
INSERT INTO TESTA VALUES ( 'CONF2','2,3,4','E2');
SELECT cola, extname, value as split_metric FROM TESTA AS A CROSS APPLY DBO.FNDSTRINGTOTABLE(A.METRICSET,',') B
Yes it will work perfect. I think we can also use Coalesce .. I will try sometime and return the snippet back here.
ReplyDelete