Split Column

Question

Split Column

rocky_498

SSCertifiable

Points: 6532
More actions
July 27, 2011 at 7:05 pm

#245122

Hi guys,
Here is my data
Add,Street_Number,Street_Name
123 Johnson St,Null,Null
22 Main Blvd, Null, Null
I want to split "Add" column end result should be like this
Add,Street_Number,Street_Name
123 Johnson St,123,Johnson
22 Main Blvd, 22, Main Blvd
Please guide me how i can accomplish this one in t-sql.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

rocky_498 (7/27/2011)
Hi guys,
Here is my data
Add,Street_Number,Street_Name
123 Johnson St,Null,Null
22 Main Blvd, Null, Null
I want to split "Add" column end result should be like this
Add,Street_Number,Street_Name
123 Johnson St,123,Johnson
22 Main Blvd, 22, Main Blvd
Please guide me how i can accomplish this one in t-sql.
Thanks.

What would you like to do with P.O. BOX 128?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Jeff thanks for reply and good question, but in my case i don't have any PO Box Address.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Ok, Rocky... here's some "starter" code that does what you want with the data you posted (see the first link in my signature line below for how to post data in the future... it really helps you). I say "starter" code because I believe you're going to find that splitting addresses without a full street reference to compare to is going to be a wee bit more challenging than you might currently think.

Details are in the code. I recommend you lookup "LIKE" in Books Online to find out about why the lookup pattern I used works like it does.

--===== Create a test table for demonstration and populate it.

-- This is NOT a part of the solution.

CREATE TABLE #Address

(

StreetAddress VARCHAR(50),

StreetNumber VARCHAR(15),

StreetName VARCHAR(50)

)

;

INSERT INTO #Address

(StreetAddress)

SELECT '123 Johnson St' UNION ALL

SELECT '22 Main Blvd'

;

--===== Do the split based on the position of the first non-digit position

-- and store the result back in the table.

UPDATE #Address

SET StreetNumber = LEFT(StreetAddress, PATINDEX('%[^0-9]%',StreetAddress)-1),

StreetName = LTRIM(SUBSTRING(StreetAddress,PATINDEX('%[^0-9]%',StreetAddress),8000))

;

--===== Show what we have after the split

SELECT * FROM #Address

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rocky_498 SSCertifiable Points: 6532 More actions · Answer 4

rocky_498

SSCertifiable

Points: 6532

July 28, 2011 at 10:04 am

#1360277

Thanks Jeff!

Its work fine.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Thanks for the feedback, Rocky.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)