Details Of Split String in Sql :: SQL Server 2014 Version(12.0.4100.1) SQL Server 2008 Version(10.50.1617.0)

STRING_SPLIT (Transact-SQL) is avail able from SQL Server 2016 

As there is no built in split string function in sql server 2014, 2010, 2008 I have build the bellow function from getting help from stackloverflow.com . 

1. This is single paged query to under standing split string.

1. Declare @products varchar(200) = '1,2,3,4,5,6,7,8,9,10'
2. -- SET @products = SUBSTRING(@products, LEN('ss' + ',') + 1, LEN(@products))
3. --select @products
4. Declare @individual varchar(20) = null
5. WHILE LEN(@products) > 0
6. BEGIN
7. IF PATINDEX('%,%',@products) > 0
8. BEGIN
9. SET @individual = SUBSTRING(@products, 0, PATINDEX('%,%',@products))
10. SELECT @individual
11. SET @products = SUBSTRING(@products, LEN(@individual + ',') + 1,
12. LEN(@products)-1)
13. END
14. ELSE
15. BEGIN
16. SET @individual = @products
17. SET @products = NULL
18. SELECT @individual
19. END
20. END
21. select @products

2. Here is a Table Valued Function in sql server which will split a string according to a certain delimiter and returns list of integer values. For this shake your strings must need to be consist of numeric values separated by the desired characters.

1. USE [Haicang]
2. GO
3. /****** Object:  UserDefinedFunction [dbo].[SplitString]    Script Date: 06-Sep-2016 11:42:43 AM ******/
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
8. ALTER FUNCTION [dbo].[SplitString] (@inputString nVARCHAR(500), @delimiter nVARCHAR(1))
9. RETURNS @listOfTablesArrangementIDs table (id int) 
10. AS BEGIN
11. /******************************************/  
12. Declare @individual int 
13. Declare @individualString varchar(10) 
14.  
15. WHILE LEN(@inputString) > 0
16. BEGIN
17.     IF PATINDEX('%'+@delimiter+'%', @inputString) > 0
18.     BEGIN
19.    set @individualString =  SUBSTRING(@inputString, 0, PATINDEX('%'+@delimiter+'%',@inputString)) 
20.         SET @individual =CONVERT(INT, @individualString)
21.         insert @listOfTablesArrangementIDs(id) values(@individual)
22.         SET @inputString = SUBSTRING(@inputString, LEN(@individualString +  @delimiter) + 1,  LEN(@inputString) + 1)
23.     END
24.     ELSE
25.     BEGIN
26.         SET   @individualString = @inputString
27. SET @individual =CONVERT(INT, @individualString)
28.         insert @listOfTablesArrangementIDs(id) values(@individual)
29.         SET @inputString = NULL
30.         
31.     END
32. END 
33.     RETURN  
34. END

//And Now Here how you have to call the SplitString function. From any query page just run it .

1. declare  @listOfTablesArrangementIDs table (id int) 
2. insert into @listOfTablesArrangementIDs   
3. SELECT * FROM [dbo].[SplitString] ('1,23,4343'  ,',')
4. select * from @listOfTablesArrangementIDs

3. Here is a Table Valued Function in sql server which will split a string according to a certain delimiter and returns list of strings values.  

1. USE [ProjectInfoDB]
2. GO
3. /****** Object:  UserDefinedFunction [dbo].[SplitString]    Script Date: 11/19/2018 6:58:46 PM ******/
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
8. -- =============================================
9. -- Author: <Muhammad Ashikuzzaman,,Name2>
10. -- Create date: <2018-11-19,,>
11. -- Edit date: < >
12. -- Description: <Initial server: Server: 192.168.13.253 ,,>
13. -- =============================================
14. ALTER FUNCTION [dbo].[SplitString] (@inputString NVARCHAR(500), @delimiter NVARCHAR(1))
15. RETURNS @listOfTablesArrangementIDs table (id nvarchar(20)) 
16. AS BEGIN
17. /******************************************/  
18. Declare @individual nvarchar(20) 
19. Declare @individualString nvarchar(20) 
20. WHILE LEN(@inputString) > 0
21. BEGIN
22.     IF PATINDEX('%'+@delimiter+'%', @inputString) > 0
23.     BEGIN
24.      set @individualString =  SUBSTRING(@inputString, 0, PATINDEX('%'+@delimiter+'%',@inputString)) 
25.         SET @individual =CONVERT(nvarchar(20), @individualString)
26.         insert @listOfTablesArrangementIDs(id) values(@individual)
27.         SET @inputString = SUBSTRING(@inputString, LEN(@individualString +  @delimiter) + 1,  LEN(@inputString) + 1)
28.     END
29.     ELSE
30.     BEGIN
31.         SET   @individualString = @inputString
32.   SET @individual =CONVERT(nvarchar(20), @individualString)
33.         insert @listOfTablesArrangementIDs(id) values(@individual)
34.         SET @inputString = NULL 
35.     END
36. END 
37.     RETURN  
38. END

//And Now Here how you have to call the SplitString function. From any query page just run it .

1. declare  @listOfTablesArrangementIDs table (id nvarchar(20)) 
2. insert into @listOfTablesArrangementIDs   
3. SELECT * FROM [dbo].[SplitString] ('1,Allah,asdasd'  ,',')
4. select * from @listOfTablesArrangementIDs

ADmi