Select query from two tables join using where condition with like clause

Transact-SQL
#1

up.project_nos is the column from table usersprojectlist up

i want to use like '%trim(up.project_no)%' as a join criteria to qb tables for column customer_name

can you please help, how to actually use the like clause in where .

select * from [QB_TIMERLIST]  qb, usersprojectlist up
where qb.customer_name Like ' + char(39)+ CHAR(37) + rtrim(up.project_nos) + CHAR(37) + char(39)';

Thank you very much for the helpful info.

#2

Yes: don't. Split the project_nos column and do a direct join to the customer_name column.

#3

you mean this way, is this the proper join with like clause. Thank you.

select * from [QB_TIMERLIST] qb join usersprojectlist up on
(qb.customer_name like '%up.project_nos%')

#4

I am using both queries below with direct join but no result coming even though there are matches.

there is project_no = 2016-0775 in usersprojectlist table

in qb table custome_name = 'GFleming:2016-0775 JIRR Expansion (3rd track)'

select * from [QB_TIMERLIST] qb join usersprojectlist up on
qb.customer_name Like ' + char(39)+ CHAR(37) + rtrim(up.project_no) + CHAR(37) + char(39)';

select * from [QB_TIMERLIST] qb join usersprojectlist up on
(qb.customer_name like '% rtrim(up.project_no) %') ;

#5

Change this:

to this:

select *
  from [QB_TIMERLIST] as qb
       inner join usersprojectlist as up
               on qb.customer_name like '%'+rtrim(up.project_no)+'%'
;

Edit: corrected like sentence

#6

Thank you it worked.

#7

No. For performance and accuracy, you want to do this:

SELECT * 
FROM [QB_TIMERLIST] qb
INNER JOIN dbo.DelimitedSplit8K(up.project_nos, ',') ds ON 
    qb.customer_name = LTRIM(RTRIM(Item))

If one name is "Jacob" and another is "Jacobin", wouldn't your %-based code match both when you searched for 'Jacob'?