Create a function that will accept the "text string" and "character" where we want to split the string:
ALTER FUNCTION [dbo].[funSplit]
(
@Texto varchar(max),
@Splitter varchar(3)
)
RETURNS
@Lista TABLE
(
rowData varchar(8000)
)
AS
BEGIN
DECLARE @Pos Smallint
While len(@Texto)>0 BEGIN
SET @Pos = Patindex('%'+@Splitter+'%',@Texto)
IF @Pos > 0 BEGIN
INSERT INTO @Lista
SELECT left(@Texto, @Pos-1)
SET @Texto = right(@Texto, len(@Texto)-@Pos)
END
ELSE BEGIN
INSERT INTO @Lista
SELECT @Texto
SET @Texto = ''
END
End
RETURN
END
GO
Now call the function pass the "sample string" & "character" to split as shown below:
The output of the query would look like below, that is our desired output:
