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