Question - SQL Server Forums

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

Other Forums

Other Topics

Question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-25 : 07:28:41

Michael writes "Want help understand what they want me to do here?

#2. Convert contact data from the following table:

Customer |Notes
--------------------------------------------------
XYZ Co. | 01/04/04 - called customer
XYZ Co. | 02/02/2004 - customer placed new order
XYZ Co. | 02/15/04 - customer cancelled order
ABC Corp. | 05/07/2003 - cold call
ABC Corp. | 05/18/2003 - customer placed new order
--------------------------------------------------

...into the new structure

customer_contacts
-----------------
id int,
customer varchar(40),
contact_date datetime,
contact_notes varchar(255)

NOTE: How do I strip the Date out of the Notes field?"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-25 : 08:55:38

[code]select ltrim(rtrim(substring(x.a, charindex('|',x.a)+1, charindex('-',x.a)-(charindex('|',x.a)+1)))) as contact_date
from
(
select 'XYZ Co. | 01/04/04 - called customer' as a union all
select 'XYZ Co. | 02/02/2004 - customer placed new order' as a union all
select 'XYZ Co. | 02/15/04 - customer cancelled order' as a union all
select 'ABC Corp. | 05/07/2003 - cold call' as a union all
select 'ABC Corp. | 05/18/2003 - customer placed new order' as a
) x[/code]

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-25 : 10:07:20

http://sqlteam.com/forums/topic.asp?TOPIC_ID=65290

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources