Monday, September 5, 2016

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


1. This is single paged query to under stand 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

  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


ADmi

Post a Comment

Add Choice