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.
LVL 1
sqlman08Asked:
Who is Participating?
 
Sham HaqueConnect With a Mentor Senior SAP CRM ConsultantCommented:
UPDATE TableA
SET [Merged] = CONVERT(varchar(10),PartNumber) + CASE WHEN [Option] IS NULL THEN '' ELSE '#'+[option] END
0
 
sqlman08Author Commented:
sorry like this

part_number                          option            Merged
45556            GHJ            45556#GHJ
54566                        54566
45666            UOY            45666#UOY
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
What is the datatype of the column 'merged' seems lilke it is Varbinary. Change it to Varchar it will work
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
UPDATE TableA
SET [Merged] = CONVERT(varchar(10),PartNumber) +  ISNULL('#'+[Option], '')
0
All Courses

From novice to tech pro — start learning today.