Hi, I wanted to know if it is possible to carry out a split as shown below, since I need to receive a string of words separated by commas; So far I have not been served by the methods I have seen on the internet.
SELECT *
FROM BASE
WHERE Doc in (SELECT * FROM STRING_SPLIT(@DocV,','))
You could do this, which automatically separates by the character ,
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
and you use it:
SELECT * FROM dbo.splitstring('91,12,65,78,56,789')
You can use this function.
CREATE FUNCTION [Split]
(
@RowData NVARCHAR(MAX) ,
@SplitOn NVARCHAR(5)
)
RETURNS @ReturnValue TABLE (Indice INT, Data NVARCHAR(MAX) ) AS
BEGIN
DECLARE @Counter INT
SET @Counter = 1
WHILE ( CHARINDEX(@SplitOn, @RowData) > 0 )
BEGIN
INSERT INTO @ReturnValue (Indice, data )
SELECT Indice = @Counter, Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, CHARINDEX(@SplitOn, @RowData) - 1)))
SET @RowData = SUBSTRING(@RowData, CHARINDEX(@SplitOn, @RowData) + 1, LEN(@RowData))
SET @Counter = @Counter + 1
END
INSERT INTO @ReturnValue ( Indice, data )
SELECT Indice = @Counter , Data = LTRIM(RTRIM(@RowData))
RETURN
END;