CODEWITHSUNDEEP
sql-servert-sqlstring
riya reddy
riya reddy

Reputation:

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

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?

Upvotes: 1

Views: 7805

Answers (5)

RolandTumble
RolandTumble

Reputation: 4703

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.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135121

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

Upvotes: 8

msvcyc
msvcyc

Reputation: 2603

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.

Upvotes: 0

eeeeaaii
eeeeaaii

Reputation: 3460

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

Upvotes: 0

Vincent Buck
Vincent Buck

Reputation: 17152

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.

Upvotes: 0

Related Questions