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?
Mark WillsConnect With a Mentor Topic 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,'')
Scott PletcherConnect With a Mentor Senior DBACommented:
UPDATE dbo.table_name
SET Col2 = Col1 + Col2
WHERE Col2 NOT LIKE Col1 + '%'
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.