Complex SQL Queries With Split() Function in SQL

Introduction

I have come across a problem while doing development. I have searched the internet for a solution to 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 are as follows.

Problem

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

String Example

 "(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

)

I hope this will help you to solve your problem.