Split() Function examlple in SQL 2008

 

Requirement: You 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

SqlQueries.jpg

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]

                 ,)

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,

        [varchar](50) NULL

)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s