Sort, Filter Height In MySQL

I came across a situation where I had to filter out a height column so that I will only get rows between, say 5′6 as the minimum. Now, if let us say you are required to filter out all rows with a height between 5′3 and 6′5, you cannot possibly enumerate each height in the IN keyword of SQL. Well, you can, but that is not maximizing the capabilities of programming then. What you can do is extract the values of the height column and get the feet and inches value respectively. With that, you can multiply the foot value by 12 since 1 foot is equal to 12 inches, then add the remaining inches to the resulting product.

Using the MySQL function substring_index(), you can retrieve a substring value at the specified index. So for example, if your height values are in this format 5′6, you can do this in MySQL …

where (substring_index(height, "'", 1) * 12 + substring_index(height, "'", -1)) >= 66

The MySQL query above filters out rows that have a minimum height of 5′6.

MySQL: Using Column Name Inside The LIKE Keyword

When using the LIKE keyword, I always get to do it this way, LIKE (’%STRING%’). When the time came for me to use a column name instead since all string values to search for is in a table in my database, I tried to just replace the string value with the column name like this LIKE (%table.column_name%). The result was pretty obvious. It gave me an error.

As a string is required in this situation, I thought of trying the CONCAT() function since that outputs a string. And it worked! Hehehe. You can do it like this

LIKE CONCAT('%', TABLE.column_name)