create multiple columns from one column...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

create multiple columns from one column...

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-09 : 16:41:28

I have the following returned from a column called header in a database:

FROM: Patrick Gundlach Sent From IP: TR2857 Sent At: 2/1/2006 10:52:32 AM TO: Greg Baxter CC: SUBJECT: (RE) (RE)

I would like to build fourcolumns from this one column:

From:
SentAt:
To:
Subject:

Anyone have a quick way to accomplish this

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-09 : 17:04:54

Here is an approach:

declare @string varchar(max)
set @string=
'FROM: Patrick Gundlach Sent From IP: TR2857 Sent At: 2/1/2006 10:52:32 AM TO: Greg Baxter CC: SUBJECT: (RE) (RE)'
select @string

select
substring(@string,patindex('%FROM:%',@string),patindex('%Sent From IP:%',@string)-1) as [From],
substring(@string,patindex('%Sent At:%',@string),patindex('%TO:%',@string)-1-patindex('%Sent At:%',@string)) as [Sent At],
substring(@string,patindex('%TO:%',@string),patindex('%CC:%',@string)-1-patindex('%TO:%',@string)) as [To],
substring(@string,patindex('%SUBJECT:%',@string),255) as [Subject]

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-09 : 17:35:33

Simple modification in Webfred's query:

select
substring(@string,patindex('%FROM:%',@string)+6,patindex('%Sent From IP:%',@string)-7) as [From],
substring(@string,patindex('%Sent At:%',@string)+8,patindex('%TO:%',@string)-8-patindex('%Sent At:%',@string)) as [Sent At],
substring(@string,patindex('%TO:%',@string)+4,patindex('%CC:%',@string)-4-patindex('%TO:%',@string)) as [To],
substring(@string,patindex('%SUBJECT:%',@string)+8,len(@string)) as [Subject]

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-09 : 18:05:25

Simple modification to sodeep's query:

select
substring(@string,patindex('%FROM:%',@string)+6,patindex('%Sent From IP:%',@string)-8) as [From],
convert(datetime,substring(@string,patindex('%Sent At:%',@string)+9,patindex('%TO:%',@string)-patindex('%Sent At:%',@string)-10)) as [Sent At],
substring(@string,patindex('%TO:%',@string)+4,patindex('%CC:%',@string)-patindex('%TO:%',@string)-5) as [To],
substring(@string,patindex('%SUBJECT:%',@string)+9,255) as [Subject]

(Removes the actual TO:, FROM:, etc. tags from data)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-09 : 18:11:14

quote:
Originally posted by Skorch

Simple modification to sodeep's query:

select
substring(@string,patindex('%FROM:%',@string)+6,patindex('%Sent From IP:%',@string)-8) as [From],
convert(datetime,substring(@string,patindex('%Sent At:%',@string)+9,patindex('%TO:%',@string)-patindex('%Sent At:%',@string)-10)) as [Sent At],
substring(@string,patindex('%TO:%',@string)+4,patindex('%CC:%',@string)-patindex('%TO:%',@string)-5) as [To],
substring(@string,patindex('%SUBJECT:%',@string)+9,255) as [Subject]

(Removes the actual TO:, FROM:, etc. tags from data)

Have you run my query. It removes Tag.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-09 : 18:19:41

quote:
Originally posted by sodeep

quote:
Originally posted by Skorch

Simple modification to sodeep's query:

select
substring(@string,patindex('%FROM:%',@string)+6,patindex('%Sent From IP:%',@string)-8) as [From],
convert(datetime,substring(@string,patindex('%Sent At:%',@string)+9,patindex('%TO:%',@string)-patindex('%Sent At:%',@string)-10)) as [Sent At],
substring(@string,patindex('%TO:%',@string)+4,patindex('%CC:%',@string)-patindex('%TO:%',@string)-5) as [To],
substring(@string,patindex('%SUBJECT:%',@string)+9,255) as [Subject]

(Removes the actual TO:, FROM:, etc. tags from data)

Have you run my query. It removes Tag.

Weird, I coulda sworn it didn't when I ran it...

However, your query does leave extra spaces in the fields. Mine takes them out and only leaves the string values.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-09 : 18:20:30

Here's another kinda cool thing you can do with computed columns:

CREATE TABLE Test(
String varchar(max),
[From] as substring(string,patindex('%FROM:%',string)+6,patindex('%Sent From IP:%',string)-8),
[SentAt] as convert(datetime,substring(string,patindex('%Sent At:%',string)+9,patindex('%TO:%',string)-patindex('%Sent At:%',string)-10)),
[To] as substring(string,patindex('%TO:%',string)+4,patindex('%CC:%',string)-patindex('%TO:%',string)-5),
[Subject] as substring(string,patindex('%SUBJECT:%',string)+9,255))

insert into test (string)
values ('FROM: Patrick Gundlach Sent From IP: TR2857 Sent At: 2/1/2006 10:52:32 AM TO: Greg Baxter CC: SUBJECT: (RE) (RE)')

select [from], [sentat], [to], [subject] from test

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-09 : 23:45:01

Thanks everyone for all the suggestions will be trying all the different options when I get back into work on Monday. Hope everyone enjoys their weekends.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-09 : 23:48:02

I also just want to confirm that since I am several hundred rows in this table, and I want to format each row based on the multiple columns, do I just pass in the row name instead of @string?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-10 : 00:04:53

quote:
Originally posted by sodeep

Simple modification in Webfred's query:

select
substring(@string,patindex('%FROM:%',@string)+6,patindex('%Sent From IP:%',@string)-7) as [From],
substring(@string,patindex('%Sent At:%',@string)+8,patindex('%TO:%',@string)-8-patindex('%Sent At:%',@string)) as [Sent At],
substring(@string,patindex('%TO:%',@string)+4,patindex('%CC:%',@string)-4-patindex('%TO:%',@string)) as [To],
substring(@string,patindex('%SUBJECT:%',@string)+8,len(@string)) as [Subject]

in this query u can pass the urcolumn name instead of @string value

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-12 : 08:51:39

Thanks to everyone, the above logic is working perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources