Thanks to Microsoft finally SQL server have predefined tabbed value function which can now easily split delimiter likes comma, tab or any other char and can return Table.
Example:-
STRING_SPLIT with Scalar Variable?
DECLARE @Birds VARCHAR(50); SET @Birds = 'Cock,Crane,Crow,Cuckoo,Dove,Duck,Eagle,Hawk Cuckoo'; SELECT value FROM STRING_SPLIT(@Birds, ','); DECLARE @DelimitedValues VARCHAR(MAX); SET @DelimitedValues = ' 21968 2323 23 23 23234 234 4234 234 '; SELECT CONVERT(INT, REPLACE(value, CHAR(13), '')) AS OrderID FROM STRING_SPLIT(@DelimitedValues, CHAR(10)) WHERE REPLACE(value, CHAR(13), '') <> '';How to split table column's value, if it contains delimited value?
CREATE TABLE DelimitedBeing ( [Type] VARCHAR(50), [Names] VARCHAR(50) ); INSERT INTO dbo.DelimitedBeing ( Type, Names ) VALUES ('Bird', 'Cock,Crane,Crow,Cuckoo,Dove,Duck,Eagle,Hawk Cuckoo'), ('Animal', 'Bear,Boar,Bull,Centipede,Chicken,Cobra,Crab,Crane'); SELECT * FROM dbo.DelimitedBeing; SELECT [Type], [value] AS Name FROM dbo.DelimitedBeing CROSS APPLY STRING_SPLIT(Names, ',');How JOIN works with Cross apply & STRING_SPLIT?
DECLARE @Being VARCHAR(50); SET @Being = 'Bird,Animal'; --DECLARE @title varchar(50); SET @title = 'Bird'; SELECT Type, S.value AS Name FROM dbo.DelimitedBeing D CROSS APPLY STRING_SPLIT(D.Names, ',')S JOIN STRING_SPLIT(@Being, ',')T ON D.Type = T.value;Set Custom Parameter with delimited value, so that for empty value will have
all records and for specific values limited records.
DECLARE @LineDelimitedValues VARCHAR(MAX); SET @LineDelimitedValues = ' 43863223 '; /*Calling Examples 1____________________________To get all records SET @LineDelimitedValues = ' '; 2____________________________To get all recordsTo get specific records SET @LineDelimitedValues = ' 43863223 '; */ /*----------------------------------------------- Set custom condition to process based on @scalered variable*/ DECLARE @IsConditionTrue BIT; SET @IsConditionTrue = 1; DECLARE @IsTrue BIT; SET @IsTrue = 0; IF NOT EXISTS(SELECT TOP 1 * FROM STRING_SPLIT(@LineDelimitedValues, CHAR(10)) WHERE REPLACE(value, CHAR(13), '') <> '' ) BEGIN PRINT 'True' SET @IsConditionTrue = 1 SET @IsTrue = 1 END /*----------------------*/ SELECT * FROM sys.tables WHERE object_id IN ( SELECT CONVERT(INT, REPLACE(value, CHAR(13), '')) FROM STRING_SPLIT(@LineDelimitedValues, CHAR(10)) WHERE REPLACE(value, CHAR(13), '') <> '' ) OR @IsConditionTrue = @IsTrueHow to get row Number and Preserve sequenceDECLARE @splitedValue VARCHAR(MAX) = 'a,d,c,b,f'; SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO FROM STRING_SPLIT(@splitedValue, ',') SELECT *,ROW_NUMBER() OVER (ORDER BY value) AS SNO FROM STRING_SPLIT(@splitedValue, ',') /* (6137 rows affected) SQL Server Execution Times: CPU time = 31 ms, elapsed time = 31 ms. (6137 rows affected) SQL Server Execution Times: CPU time = 63 ms, elapsed time = 62 ms. Solution 1: Preserve sequence, more efficient. Solution 2: Not preserving sequence. */Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql