How do I split an address string in T-SQL?

Question

I want to split a column into 4 columns based on column.

Eg: column value includes 'City_Name' , 'State' ,Zipcode' ,'Country'

I want to split it into 4 different columns like City_Name, State, Zipcode, Country.

How can I do this using T-SQL?

Answer 1

If you've got some obvious separators in your original column, you can either write a user-defined function or use some inline regex to extract your data into 4 columns. How you do that depends on:

• your data and especially how homogeneous it is
• your RDMBS

You may want to let us see some sample data for additional information.

Answer 2

step 1: create the four new columns in your database table. since you're adding new columns to an existing table, they have to have default values (for example, the empty string)

step 2: run an update query that updates your four new columns based on your old column. Use the SQL string functions to do this (can look them up in the reference, for example, SUBSTRING)

step 3: drop the old column

Answer 3

Create a new table with the desired fields. Traverse through the records in the original table and split the value in that one column by the delimiter. For splitting, you could use charindex, substring and other string manipulation functions. You can code this logic in a SP or a function.

Answer 4

assuming you always have 3 commas in that column you can use the PARSENAME function

create table #test(Col varchar(100))
insert #test values('City_Name,State,Zipcode,Country')
insert #test values('New York City,NJ,10028,United States')


select parsename(replace(Col,',','.'),4) as City_Name,
parsename(replace(Col,',','.'),3) as State,
parsename(replace(Col,',','.'),2) as Zipcode,
parsename(replace(Col,',','.'),1) as Country
from #test

output

City_Name   State Zipcode Country
New York City   NJ 10028 United States

Answer 5

Tested with your sample string:

CREATE FUNCTION fn_PARM (@Parm nvarchar(2000))
RETURNS @TParm TABLE (City_Name nvarchar(100),State nvarchar(100),Zipcode nvarchar(100),Country nvarchar(100)) 
AS  
BEGIN 

DECLARE @City_Name nvarchar(100)
    ,@State nvarchar(100)
    ,@Zipcode nvarchar(100)
    ,@Country nvarchar(100)
    ,@CommaPosition1 tinyint
    ,@CommaPosition2 tinyint

    SELECT @CommaPosition1 = charindex(',',@Parm)
    IF (@CommaPosition1>0) 
    BEGIN
     SELECT @City_Name = ltrim(rtrim(SUBSTRING(@Parm,1,@CommaPosition1 - 1)))
     SELECT @CommaPosition2 = @CommaPosition1 + 1

     SELECT @CommaPosition1 = charindex(',',@Parm,@CommaPosition2)
     IF (@CommaPosition1>0) 
     BEGIN
      SELECT @State = ltrim(rtrim(SUBSTRING(@Parm,@CommaPosition2 + 1,@CommaPosition1 - (@CommaPosition2 + 1))))
      SELECT @CommaPosition2 = @CommaPosition1 + 1

      SELECT @CommaPosition1 = charindex(',',@Parm,@CommaPosition2)
      IF (@CommaPosition1>0) 
      BEGIN
       SELECT @Zipcode = ltrim(rtrim(SUBSTRING(@Parm,@CommaPosition2 + 1,@CommaPosition1 - (@CommaPosition2 + 1))))
       SELECT @Country = ltrim(rtrim(SUBSTRING(@Parm,@CommaPosition1 + 1,LEN(@Parm))))
      END
     END
    END

    INSERT INTO @TParm (City_Name, State, Zipcode,Country) 
     VALUES (@City_Name, @State, @Zipcode, @Country)

    RETURN 
END 
GO

Simple usage:

select * from fn_PARM('Delhi , Delhi ,34567 ,IND' )

For use in a query, see This MSDN article about using APPLY.