CONTAINS or LIKE doesn't seem to work mysql lite

Question

2.50/5 (2 votes)

See more:

Can someone help in resolving one of following commented lines please?
I tried with LIKE as well as CONTAINS.
Got no result with like but the values are there.

Also, CONTAINS doesn't seem to work in mySQL LIte.

SQL

<pre>CREATE TABLE PxpAsPerPolarBookings AS SELECT pxp.invoice , PolarCruiseBrandPax.bookingRef 
From pxp, PolarCruiseBrandPaxFile2 
where 
//pxp.invoice CONTAINS PolarCruiseBrandPaxFile2.bookingRef;
//OR
//pxp.invoice like '%'+ PolarCruiseBrandPaxFile2.bookingRef +'%';

What I have tried:

I tried using like statement but since its not a constant field, how to we do it if not this:

pxp.invoice like '%'+ PolarCruiseBrandPaxFile2.bookingRef +'%';

Posted 9-Apr-20 22:24pm

Member 12858897

Updated 9-Apr-20 22:52pm

Add a Solution

Comments

#realJSOP 10-Apr-20 4:35am

What's the error you're getting? "Doesn't seem to work doesn't tell us anything at all.

Richard MacCutchan 10-Apr-20 4:43am

Why are the last three lines commented out?

#realJSOP 10-Apr-20 6:25am

Probably because they "don't work".

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2020-04-09T22:53:00

Solution 1

One reason for not returning any data could be if the column is case sensitive. You can try the following

SQL

SELECT pxp.invoice, 
       p2.bookingRef 
FROM pxp, 
     PolarCruiseBrandPaxFile2 p2
WHERE pxp.invoice COLLATE UTF8_GENERAL_CI LIKE '%' + p2.bookingRef + '%';

Posted 9-Apr-20 22:53pm

Wendelius

Comments

Maciej Los 10-Apr-20 5:55am

5ed!

Wendelius 10-Apr-20 6:29am

Thanks!

#realJSOP 10-Apr-20 6:27am

But LIKE eliminates the need to be concerned with case sensitivity (or it does in SQL Server).

Wendelius 10-Apr-20 6:29am

Depends on the selected collation, also in SQL Server. Also note that it is not operator specific, the same applies to equality etc.

CONTAINS or LIKE doesn't seem to work mysql lite

1 solution

Solution 1

Add your solution here

Preview 0

CONTAINS or LIKE doesn't seem to work mysql lite

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us