CODEWITHSUNDEEP
mysqlsqlselect
LIGHT
LIGHT

Reputation: 5712

LEN() of % in MySQL Query

Example Data:

Sample #1

<br/>Out at Jan 01, 2019, 5:27 pm <small>by Shyam</small>
<br/>In at Jan 02, 2019, 2:06 pm <small>by Ram</small>

Sample #2

<br/>Out at Jan 01, 2019, 01:22 pm <small>by Ram</small>
<br/>In at Jan 02, 2019, 2:22 pm <small>by Shyam</small>

I need query to return just the Sample #2 as I'm looking for data by Ram at Jan 01, 2019.

I have this query:

SELECT * FROM `tbl` WHERE `log` LIKE '%Jan 01, 2019 % Ram %';

That query is returning both Sample #1 and Sample #2 as the length of % after date is not defined.

I need to figure-out a way to give % character length

Like:

SELECT * FROM `tbl` WHERE `log` LIKE '%Jan 01, 2019 LEN(%)=3 Ram %';

I tried to achieve the required fields with other queries but all failed. Is there anyway to count string length of %?

Upvotes: 2

Views: 64

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37347

You can use _ wildcard representing any characters. Text between date and by Ram is 5:27 pm <small>, so you can use _______________ (exactly 15 characters), so pattern could be:

SELECT * 
FROM `tbl`
WHERE `log` LIKE '%Jan 01, 2019 __________by Ram %'
OR `log` LIKE '%Jan 01, 2019 ___________by Ram %'; -- if the times is 12:12 for example

Since this is log column (I assume) it's structured nicely and the numbers of characetrs assumed won't vary due to some typos, etc.

Upvotes: 1

Adder
Adder

Reputation: 5868

You can use an underscore as placeholder for one character. Repeat them to match several characters. Or just use HTML:

SELECT * FROM `tbl` WHERE `log` LIKE '%Jan 01, 2019 <small>by Ram</small>';

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use a regular expression. Something like this:

SELECT *
FROM `tbl`
WHERE `log` REGEXP 'Jan 01, 2019 .{3} Ram ';

The . represents any character. The {3} says it can be repeated up to three times.

I'm not sure where the "3" comes from. It seems like there are about 10 characters. You can put a range in like this:

SELECT *
FROM `tbl`
WHERE `log` REGEXP 'Jan 01, 2019 .{1,15} Ram ';

Upvotes: 3

Related Questions