LEN() of % in MySQL Query

mysql length of varchar
mysql length of string
mysql string length greater than
mysql substring
mysql array length
mysql concat
sql find max length of column value
mysql replace

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 %?

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 ';

MySQL LENGTH() Function, MySQL LENGTH() Function. ❮ MySQL Functions. Example. Return the length of the string, in bytes: SELECT LENGTH("SQL  Ian on May 1, 2018 In SQL Server, you can use the LEN () function to return the number of characters in a string. This is a Transact-SQL function that can also be used in Azure databases. In MySQL, you need to use the CHAR_LENGTH () function.

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.

MySQL LENGTH() function, MySQL LENGTH() returns the length of a given string. Syntax: LENGTH SELECT pub_name,LENGTH(pub_name) FROM publisher;. Sample  string functions ascii char charindex concat concat with + concat_ws datalength difference format left len lower ltrim nchar patindex quotename replace replicate reverse right rtrim soundex space str stuff substring translate trim unicode upper numeric functions abs acos asin atan atn2 avg ceiling count cos cot degrees exp floor log log10 max

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>';

MySQL String Length, This tutorial shows you how to use MySQL string length functions to get the length of SET @s = CONVERT('MySQL String Length' USING ucs2); SELECT  The SQL LEN function is a SQL String Function, used to find the length of a specified expression.Or we can say, The SQL server LEN Function will count the number of characters inside the given expression and returns the INT results.

MySQL 8.0 Reference Manual :: 12.7 String Functions and , mysql> SELECT CHAR(77,121,83,81,'76'); -> 'MySQL' mysql> SELECT CHAR(77 This means that for a string containing five 2-byte characters, LENGTH()  Definition and Usage. The LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length.

LEN function in MSSQL and it's equivalent in MySQL, LEN Function in MS SQL Server and it's counterpart in MySQL Whereas in MySQL LENGTH function achieves the same result. select datalength('demo ') SELECT City, CHAR_LENGTH(City) AS len FROM STATION ORDER BY CHAR_LENGTH(City) ASC, City LIMIT 1 City with the longest name: SELECT City, CHAR_LENGTH(City) AS len FROM STATION ORDER BY CHAR_LENGTH(City) DESC, City LIMIT 1 You can combine them into a single statement with UNION ALL:

What's the LEN() Equivalent in MySQL?, In MySQL, you need to use the CHAR_LENGTH() function. Examples. Here's an example of each. SQL Server (T-SQL). SELECT LEN('Cat') AS '  How it works. First, we convert the MySQL String Length string into ucs2 character set, which is UCS-2 Unicode that holds 2-byte characters.; Second, we use the CHAR_LENGTH and LENGTH functions to get the length of the @s string in bytes and in characters.

Comments
  • Is regexp valid in version of MySQL below 8.0 ?
  • It returned empty result. May be because there are characters before Jan and after Ram? I used to denote it with %
  • @MichałTurczyn . . . Yes. It has been part of MySQL since basically forever, I think (I mean all the 5+ versions).
  • @LIGHT . . . No. A regular expression is not anchored to the beginning of the string. It matches anywhere in the string. The issue is probably the number of intervening characters -- it is also possible that . does not match newlines if they are in the string.
  • Problem is, we don't have exact number of _ because the time can sometimes be 2:22 or 12:22
  • @LIGHT Then use or with one _ less :)
  • That certainly did work for me and I upvoted your answer. But I think i'll go with REGEXP as right answer for community's sake. Thank you @Michal