IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'Split_String') AND xtype IN (N'FN', N'IF', N'TF') ) DROP FUNCTION [dbo].[Split_String] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[Split_String] ( @InputList VARCHAR(8000) ='',-- List of delimited items @Delimiter NVARCHAR(1) = ',' -- delimiter that separates items ) RETURNS @List TABLE ( [Index] INT NOT NULL IDENTITY(0, 1) PRIMARY KEY, [Value] Varchar(8000) NULL ) BEGIN DECLARE @Item VARCHAR(8000) WHILE CHARINDEX(@Delimiter, @InputList, 0) <> 0 BEGIN SELECT @Item = RTRIM(LTRIM(SUBSTRING(@InputList, 1, CHARINDEX(@Delimiter, @InputList, 0) - 1))), @InputList = RTRIM(LTRIM(SUBSTRING(@InputList, CHARINDEX(@Delimiter, @InputList, 0) + LEN(@Delimiter), LEN(@InputList)))) IF LEN(@Item) > 0 INSERT INTO @List SELECT @Item END IF LEN(@InputList) > 0 INSERT INTO @List SELECT @InputList -- Put the last item in RETURN END GO select * from dbo.Split_String('a,b,c,d',',') select * from dbo.Split_String(' ', ' ')
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter