How to split a string in sql server 2008 using stored procedure and insert the data to table

How to split a string in sql server 2008 using stored procedure and insert the data to table

We can split the strings in SQL Server using split identifies(_, -, |, ' ', ....). The below code-sample might help you.

Table of Contents

1. Create Function for Split Strings Using Identifier.
2. Create Procedure and call split function within procedure.

Create Function for Split Strings Using Identifier
CREATE FUNCTION dbo.Split
(
      @ListItems NVARCHAR(2000),
      @Identifier NVARCHAR(5)
) 
RETURNS @RtnValue TABLE
(
      Id INT IDENTITY(1,1),
      Value NVARCHAR(100)
)
AS 
BEGIN

WHILE (CHARINDEX(@Identifier,@ListItems)>0)
      BEGIN
         INSERT INTO @RtnValue (value)
                  SELECT Value = LTRIM(RTRIM(SUBSTRING(@ListItems,1,CHARINDEX(@Identifier,@ListItems)-1)))
                 
         IF(@Identifier = ' ')
                  SET @ListItems = SUBSTRING(@ListItems,CHARINDEX(@Identifier,@ListItems)+1,LEN(@ListItems))
        
         ELSE
                  SET @ListItems = SUBSTRING(@ListItems,CHARINDEX(@Identifier,@ListItems)+ LEN(@Identifier),LEN(@ListItems))
      END

      INSERT INTO @RtnValue (Value)SELECT Value = LTRIM(RTRIM(@ListItems))
         
 RETURN
END

Create Procedure and call split function within procedure

CREATE PROCEDURE GET_SPLITED_TABLE --'HandSet Data-Stand Alone-4G-5000 MB', '-'
(
  @ServicesList NVARCHAR(MAX),
  @identifier NVARCHAR(10)
)
AS
BEGIN
   SELECT  Id, CONVERT(NVARCHAR(400), Value) AS Services
      FROM dbo.Split(@ServicesList, @identifier)
END


Split Results as given below screen using '-' identifier



ANIL SINGH

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Google Amazon and Flipkart Book Store!

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.
ASK Questions
SQL Server NodeJs TypeScript JavaScript Angular SQL Server My Book