Sometimes data suppliers do not make their data easy to separate. Other times you might want to split some other data up.
This SQL Table function will take a string comprising a list of substrings with a specified specified delimiter and returns a table containing the substrings
This SQL Table function will take a string comprising a list of substrings with a specified specified delimiter and returns a table containing the substrings
-- =============================================
-- Author: Technology Is Not Dull
-- Create date: 19/01/2016
-- Description: Takes a delimited string list and returns a table
-- =============================================
CREATE FUNCTION [dbo].[stringToTable]
(
@string VARCHAR(200),
@delimiter char(1)
)
RETURNS
@rettable TABLE
(
stringbit varchar(50)
)
AS
BEGIN
--***# select * from dbo.stringToTable(POLYETHYLENE GLYCOL 3350; POTASSIUM CHLORIDE; SODIUM BICARBONATE; SODIUM CHLORIDE; SODIUM SULFATE ANHYDROUS',';')
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @rettable (stringbit)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
GO
