Complex SQL Queries With Split() Function in SQL 2008

I have come across a problem while doing development. I have searched the internet for a solution for the problem and have been unable to find a solution. Finally I solved the problem. So I have decided to write an article on it. The problem and solution is as follows.

Problem

I have a long string as in the following and I want to strip it and store the values into a database.

String: - "(Andhra Pradesh, AP); (Arunachal Pradesh, AR); (Assam, AS); (Maharashtra, MH)"

Database



Solutions

For the preceding problem, I have used the split function. The following is my SQL code.

My SQL statements

DECLARE @text AS VARCHAR(MAX)

SET @text = '(Andhra Pradesh, AP); (Arunachal Pradesh, AR); (Assam, AS); (Maharashtra, MH)'

SET @text = REPLACE(REPLACE(@text,')',''),'(','');

 

INSERT INTO [dbo].[country]

                 ([state]

                 ,[code])

SELECT (SELECT Data FROM Split(A.Data,',') S WHERE S.Id = 1) 'state',

       (SELECT Data FROM Split(A.Data,',') C WHERE C.Id = 2) 'code'

FROM   (

              SELECT * FROM Split(@text,';')

       ) AS A

Split function script

CREATE FUNCTION [dbo].[Split]

(    

      @RowData varchar(MAX),

      @SplitOn varchar(5)

)

RETURNS @RtnValue TABLE

(

      Id INT IDENTITY(1,1) NOT NULL,

      Data varchar(MAX)

)

AS 

BEGIN

      While (Charindex(@SplitOn,@RowData)>0)

      Begin

            Insert Into @RtnValue (Data)

            Select

                  Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

      End

      Insert Into @RtnValue (Data)

      Select Data = ltrim(rtrim(@RowData))

      Return

END

GO

Country Table script
 

CREATE TABLE [dbo].[country1]

(

       [id] [int] IDENTITY(1,1) NOT NULL,

       [state] [varchar](100) NULL,

       [code] [varchar](50) NULL

)

Hope this will help you to solve your problem.