split data into two columns

Question

Post reply

split data into two columns

PJ_SQL

SSCertifiable

Points: 6703
More actions
May 18, 2016 at 12:46 pm

#400181

Hi,
I have one table, it contains data like
One Column
ColA:
A DR
B ST
C DR
I need to split the column into two columns
ColA ColB
A DR
B ST
C DR

Viewing 10 posts - 1 through 9 (of 9 total)

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

ManicStar SSCoach Points: 17992 More actions · Answer 1

declare @Foo varchar(10)

set @Foo = 'A DR'

select LEFT(@foo, charindex(' ', @Foo)) ,substring (@foo, charindex(' ', @Foo), DATALENGTH(@foo) - charindex(' ', @Foo) + 1)

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Use DelimitedSplit8K[/url] and append the result to a final table?

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 3

Thanks for the prompt reply.

Actually my data is as below in one column:

SLVER CREEK DR

3RD ST

OAK GATE LN

ABCSDGFDG LN

WnhhkjT ST

I need to split the above data into two columns

1st Column 2nd column

SLVER CREEK DR

3RD ST

OAK GATE LN

ABCSDGFDG ST

Thanks for the help

ManicStar SSCoach Points: 17992 More actions · Answer 4

declare @Foo varchar(20)

set @Foo = 'Muffin Man LN'

select substring(@foo, 1,len(@foo) - charindex(' ', reverse(@foo))),

substring(@foo, len(@foo) - charindex(' ', reverse(@foo))+2 ,datalength(@foo) - charindex(' ', reverse(@foo))+2 )

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 5

Thank you for the reply.

My requirement changed a bit:

I have one column:

SLVER CREEK DR

3RD ST

S OAK GATE LN

ABCSDGFDG LN

Turtle WnhhkjT

I need the above data split into three columns:

ColA ColB ColC

SLVER CREEK DR

3rd ST

S OAK GATE LN

ABCSDGFDG LN

Turtle WnhhkjT

Thanks.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Thank you for the reply.

My requirement changed a bit:

I have one column:

SLVER CREEK DR

3RD ST

S OAK GATE LN

ABCSDGFDG LN

Turtle WnhhkjT

I need the above data split into three columns:

ColA ColB ColC

SLVER CREEK DR

3rd ST

S OAK GATE LN

ABCSDGFDG LN

Turtle WnhhkjT

Thanks.

the formatting is getting messed up by the forum...above is how i see it when I view your post in plain-text. can you clarify the business rules around which value goes in Column A, B and C?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 7

I have one column:

SLVER CREEK DR

3RD ST

S OAK GATE LN

ABCSDGFDG LN

Turtle WnhhkjT

E Royal Street

Values that need to be in

ColA : S,E

ColB : SILVER CREEK, 3rd, OAK GATE,ABCSDGFDG ,Turtle WnhhkjT,Royal Street

ColC:ST,LN,LN

Thanks.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

PJ_SQL (5/19/2016)
I have one column:
SLVER CREEK DR
3RD ST
S OAK GATE LN
ABCSDGFDG LN
Turtle WnhhkjT
E Royal Street
Values that need to be in
ColA : S,E
ColB : SILVER CREEK, 3rd, OAK GATE,ABCSDGFDG ,Turtle WnhhkjT,Royal Street
ColC:ST,LN,LN
Thanks.

That is an example of the output you expect but helps me not in terms of knowing why SILVER should not be in ColA when it is in the first position in terms of a space-delimited string.

If these are addresses you are trying to parae you should buy some address standardization software.

Otherwise you're starting down the road of reinventing a very expensive wheel or creating a poor facsimile that only works some of the time.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

PJ_SQL (5/18/2016)
Thanks for the prompt reply.
Actually my data is as below in one column:
SLVER CREEK DR
3RD ST
OAK GATE LN
ABCSDGFDG LN
WnhhkjT ST
I need to split the above data into two columns
1st Column 2nd column
SLVER CREEK DR
3RD ST
OAK GATE LN
ABCSDGFDG ST
Thanks for the help

If you really need to split the address from the street type, it would be much more effective and cheaper in the long run to buy some CASS Certification software, which would also validate the address as actually existing according to the USPS.

--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)