sqlman08
asked on
Merge Columns
Hi,
I'm trying to update the following a column where if part number has an option to put a '#' between the middle and concatenate the two columns together, else if the part number hasn't a option to show the part number only i.e
part_number option Merged
45556 GHJ 45556#GHJ
54566 54566
45666 UOY 45666
I've tried the follow but no luck:
Update TableA
set MERGED = '[PartNumber] & IIf(IsNull(Option),"","#" & PartNumber)'
Error Message
Disallowed implicit conversion from data type varchar to data type varbinary, table 'TableA', column 'MERGED'. Use the CONVERT function to run this query.
I'm trying to update the following a column where if part number has an option to put a '#' between the middle and concatenate the two columns together, else if the part number hasn't a option to show the part number only i.e
part_number option Merged
45556 GHJ 45556#GHJ
54566 54566
45666 UOY 45666
I've tried the follow but no luck:
Update TableA
set MERGED = '[PartNumber] & IIf(IsNull(Option),"","#" & PartNumber)'
Error Message
Disallowed implicit conversion from data type varchar to data type varbinary, table 'TableA', column 'MERGED'. Use the CONVERT function to run this query.
What is the datatype of the column 'merged' seems lilke it is Varbinary. Change it to Varchar it will work
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
UPDATE TableA
SET [Merged] = CONVERT(varchar(10),PartNu mber) + ISNULL('#'+[Option], '')
SET [Merged] = CONVERT(varchar(10),PartNu
ASKER
part_number option Merged
45556 GHJ 45556#GHJ
54566 54566
45666 UOY 45666#UOY