split comma separated values in sql server code example
Example 1: get value comma separated sql server
DECLARE @UserMaster TABLE(
UserID INT NOT NULL,
UserName varchar(30) NOT NULL
);
INSERT INTO @UserMaster VALUES (1,'Rakesh')
INSERT INTO @UserMaster VALUES (2,'Ashish')
INSERT INTO @UserMaster VALUES (3,'Sagar')
SELECT * FROM @UserMaster
DECLARE @CSV VARCHAR(MAX)
SELECT @CSV = COALESCE(@CSV + ', ', '') + UserName from @UserMaster
SELECT @CSV AS Result
Example 2: split string by comma in sql server
CREATE FUNCTION Split
(
@delimited nvarchar(max),
@delimiter nvarchar(100)
) RETURNS @t TABLE
(
id int identity(1,1),
val nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(max)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
GO
Example 3: search from comma separated values in sql server
WHERE tablename REGEXP "(^|,)@search(,|$)"