CODEWITHSUNDEEP
sqlsql-server
istrau2
istrau2

Reputation: 367

SQL server how to do string comparison with left join

I have two tables: the entry table (one nvarchar column called entry) and the disease table (one nvarchar column called disease).

I would like to produce another table that has all the entry-disease combos where entry.entry is contained completely in disease.disease. However, I want all entries that do not have a disease that is completely contained inside of them to still appear in the results table as {entry, blank}.

I know it should probably be something like:

select entry disease from entry, disease where ...

not really sure how to write this, thanks in advance

Ok, I figured out this much:

select entry.entry, disease.disease
into new_table
from entry, disease
where CHARINDEX(entry, disease) > 0

how do I include the entries that have no match?

Upvotes: 1

Views: 3385

Answers (2)

Eray Balkanli
Eray Balkanli

Reputation: 7960

An alternative solution is to use LIKE keyword. Please see the example below:

create table #t (st varchar(20))
insert into #t values ('johnalexmichael'), ('johnmichael'),('alex'),('michael')
create table #t2 (st varchar(20))
insert into #t2 values ('alex'),('john')

SELECT t.st, t2.st
FROM #t t 
LEFT JOIN #t2 t2 on t.st like '%'+t2.st+'%'

Upvotes: 0

Yohanes Khosiawan 许先汉
Yohanes Khosiawan 许先汉

Reputation: 2449

You can use left join in this case.
It will show all entries and provide null value for the column disease.disease if an entry is not contained in any cell of disease.disease.

SELECT entry.entry, disease.disease
FROM entry LEFT JOIN disease
    ON CHARINDEX(entry.entry, disease.disease) > 0

Upvotes: 1

Related Questions