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.
Yes: don't. Split the project_nos column and do a direct join to the customer_name column.
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%')
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) %') ;
bitsmed
August 26, 2018, 12:47pm
#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
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'?