Concatenate 2 columns only if the value in one column does not exist in another

I have a SQL table and ONLY copy if col1 is already added to col2 and only concatenate 2 columns if col1 does not exist in col2. Please see attached file.

I know this is just simple query but I can't get it to work.

I really appreciate any help.

Thank you.
Queennie LAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
UPDATE dbo.table_name
SET Col2 = Col1 + Col2
WHERE Col2 NOT LIKE Col1 + '%'
Mark WillsTopic AdvisorCommented:
Well, there are a few options available to you, certainly if you want to update col2, then Scott's answer above is the way to go.

If just wanting a select you can also do
select col1,col2,iif(charindex(col1,col2) = 1, col2, cast(col1 as varchar(20))+cast(col2 as varchar(20))) [New Col1+col2]
from yourtable

Open in new window

Now, if both columns are already VARCHAR, then no need to cast as varchar.
If pre-SQL2012, then dont use IIF(), you would need to do CASE WHEN charindex(col1,col2) = 1 then col2 else col1 +col2 end [New Col1+col2]

The couple of options is 'How to identify' if col1 is already part of col2 and you can use charindex(col1,col2) = 1 or (better for 'set') col2 like col1+'%'

Now the reason for charindex() = 1 is we want to test if it starts in the first position. Using charindex > 0 will also find col1 anywhere within col2

When stringing together 2 columns, you should also check for NULL values because adding a NULL to the string will result in NULL : isnull(col1,'') + isnull(col2,'')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
select UniqueID, Col1, Col2, case when charindex(Col1,col2) = 0 then col1 + col2 else col2 end as Col3-OUTPUT
from sqltable;
Queennie LAuthor Commented:
My apology it took me days to respond.

@Scott Pletcher: The update is perfectly working. All I want it a simple query. Thank you for your help.

@Mark Willis: The query that you provided is what I needed. Simple and easy. Thank you again for your help.

Have a wonderful day!
Queennie LAuthor Commented:
Thank you again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.