Derived Column Help

Question

Post reply

Derived Column Help

tooba111

Right there with Babe

Points: 767
More actions
April 28, 2011 at 3:06 pm

#235467

Hi Guys,
Here is my source data
Address
c/o Joel Robuck
c/o Don Quick
c/o Sansone Group
c/o UCI
c/o Wendy Powers
C/O Goodwin Mgt
c/o Blake Magee Company
1101 S Cap of TX Hwy #F254
NULL
5910 Courtyard Dr #230
3701 Eagles Nest St.
NULL
2678 Henley Dr
And I want to map SOURCE.ADDRESS column to my TARGET.ADDR1 AND TARGET.ADDR2 if the source has c/o move to TARGET.ADDR2 otherwise stay in TARGET.ADDR1
Here is my expression that I am using in Derived column
ADDR1 = TRIM(SUBSTRING(Address,1,FINDSTRING(Address,"c/o",1)))
ADDR2 = UPPER(TRIM(SUBSTRING(Address,FINDSTRING(Address,”c/o",1),LEN(Address) - FINDSTRING(Address,"c/o",1) + 1)))
And I am getting nothing, Please advise me where I am wrong. Thanks for your help.

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Edit: posted advice won't work...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Is this what you are looking for ?

Declare @Address table (FullAddress varchar(500) , Address1 varchar(500), Address2 varchar(500))

insert into @Address ( FullAddress ) values ( 'c/o Joel Robuck')

insert into @Address ( FullAddress ) values ( 'c/o Don Quick')

insert into @Address ( FullAddress ) values ( 'c/o Sansone Group')

insert into @Address ( FullAddress ) values ( 'c/o UCI')

insert into @Address ( FullAddress ) values ( 'c/o Wendy Powers')

insert into @Address ( FullAddress ) values ( 'C/O Goodwin Mgt')

insert into @Address ( FullAddress ) values ( 'c/o Blake Magee Company')

insert into @Address ( FullAddress ) values ( '1101 S Cap of TX Hwy #F254')

insert into @Address ( FullAddress ) values ( NULL)

insert into @Address ( FullAddress ) values ( '5910 Courtyard Dr #230')

insert into @Address ( FullAddress ) values ( '3701 Eagles Nest St.')

insert into @Address ( FullAddress ) values ( NULL)

insert into @Address ( FullAddress ) values ( '2678 Henley Dr')

Select *

from @Address

update @Address

set Address1 = case when FullAddress like 'c/o%' then NULL else FullAddress end

, Address2 = FullAddress

Select *

from @Address

or is this what you are looking for ?

Declare @Address table

( FullAddress varchar(500)

, Address1 as (case when FullAddress like 'c/o%' then NULL

else FullAddress

end )

, Address2 as ( FullAddress )

)