python ruby-programming-language Smalltalk Vue.js Knockoutjs TypeScript JavaScript, OOPs JavaScript Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 4 Angular 2 AngularJs 1.x NodeJs Perl Programming R Programming RequireJs Rust Programming Backbonejs closure programming language go-programming-language kotlin-programming-language
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

Hi there! Welcome to my blog. My name is Anil Singh. I'm a author of this blog. For more detail about me, kindly refer to this link..
My Blogs - http://code-sample.com and http://code-sample.xyz
My Books - Interview Questions and Answers Books- Get Your Book in 15+ Digital Stores Worldwide..

You Might Also Like
www.code-sample.com/. Powered by Blogger.
ASK Questions