Discover more from OpenLampTech
OpenLamp.tech Issue #3 - 5 MySQL String Functions You Should
MySQL String Functions; Control your CodeIgniter Controllers; Transaction Isolation MySQL Deep-Dive; Best PHP Books;
Hey glad to have you here! 👍
If someone shared this newsletter with you and you are not yet subscribed, please use the Subscribe button below and join:
We have just celebrated the Thanksgiving Holiday here in the US. If you celebrated, I hope you had a great time with family and friends.
Noteworthy News
Earlier this week several companies got together and formed the PHP Foundation.
Here is a great write-up on some of the details… 🗞️
Monthly Featured Piece
Welcome to the monthly featured piece of the OpenLamp.tech newsletter. Once a month, in addition to the articles and posts I’ve curated, I’ll include a deep dive piece on one of the topics I am studying and interested in. This issue is jam-packed with plenty for everyone! I hope you enjoy it!
And now without further adieu, this month’s featured piece.
5 MySQL String Functions You Should Know
There are likely many PHP functions or language constructs you can use to accomplish any of the below functionality provided by the MySQL string functions covered in this newsletter.
That being said, if you have to or would rather perform any of this lifting in the database, here are 5 handy MySQL string functions you can add to your toolkit.
1. CONCAT()
Syntax: CONCAT(str_1, str_2, …)
Joining (term used loosely here) pieces of text and strings are quite common when working with data. MySQL supports 2 forms of string concatenation:
Quoted strings can be placed next to one another in a query and will be concatenated.
The
CONCAT()
function accepts multiple string parameters and returns the joined parameters as a single string.
📝 If any parameter value is NULL
, then CONCAT()
returns NULL
.
Here is an example of quoted strings concatenated in a SELECT
query:
SELECT 'Joshua ' 'Otwell';
Which returns:
And this query uses the CONCAT()
function:
SELECT CONCAT('Joshua', ' ','Otwell');
Notice this call to CONCAT()
has 3 parameters:
The first_name column
A space in quotes
The last_name column
I’m using a table from the well-known Sakila sample database for this next query in which I concatenate the ‘first_name’ and ‘last_name’ columns from the ‘actor’ table:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM actor
LIMIT 10;
2. INSTR()
Syntax: INSTR(str, sub_string)
The INSTR()
string function returns the integer position of the 1st occurrence of the sub_string argument found within the str argument. Although I am sure there are several use cases for this function, the one I find most useful for INSTR()
is that of a dynamic parameter for the SUBSTRING()
function. Here is a somewhat related blog post (using a different SQL dialect) with examples of pairing up these 2 string functions.
Let’s see a trivial example.
SELECT INSTR('Josh', 's');
Returns,
3
And if the sub_str is not found in the str parameter, zero is returned:
SELECT INSTR('Josh', 'f');
0
📝 Points to remember on INSTR()
:
Performs a case-insensitive search
If the sub_str is not found within the str argument,
INSTR()
returns zeroCounting starts at 1 in MySQL (SQL in general to my knowledge) not 0 (zero)
3. LENGTH()
Syntax: LENGTH(str)
There could be any number of reasons why you would need to know the length of a string. In MySQL, it is super-easy determining the length of a string with this function.
See this simple query for demonstration:
SELECT LENGTH('Josh');
Returns:
4
4. TRIM()
Syntax: TRIM([{BOTH | LEADING | TRAILING} [remove_string] FROM ] string)
Removing unwanted/unnecessary characters from string data helps ensure accurate storage of the intended value. In PHP, I use the same-named trim()
function to remove extra whitespace from user-submitted form data.
The MySQL TRIM()
function can similarly be used to strip leading and trailing whitespace from a text value. However, MySQL’s TRIM()
has some additional and differing functionality as to that of PHP's trim()
.
Related: I've written an in-depth blog post on MySQL TRIM() over on my blog, Digital Owl's Prose, so I will only gloss over TRIM()
here. But do check out this post!
In this query, I use the BOTH
keyword to remove the leading and trailing remove_string (which is an asterisk) from the string ‘*Josh*’:
SELECT '*Josh*' AS un_trimmed, TRIM(BOTH '*' FROM '*Josh*') AS trimmed;
Now we will see an example of each of the various modifier arguments for TRIM()
. Depending on which is specified in the function call, determines the returned string:
SELECT '*Josh*' AS un_trimmed,
TRIM('*' FROM '*Josh*') AS trimmed_default,
TRIM(LEADING '*' FROM '*Josh*') AS trimmed_leading,
TRIM(TRAILING '*' FROM '*Josh*') AS trimmed_trailing;
5. SUBSTRING()
Syntax: SUBSTRING(target_string, start_position, [length])
📝Note: SUBSTR()
is a (valid) synonym for SUBSTRING()
There are other various syntaxes for this string function and you can find them in the official SUBSTRING() documentation.
SUBSTRING()
returns a string portion from the target_string parameter based on the start_position parameter and optional length parameter.
📣 I'm going full-on, full-out shameless plug here. If you want to learn how SUBSTRING()
works, read this blog post I wrote: Recursive CTE Fun with SUBSTRING in MySQL.
It is literally that good. I learned so much about this function and just how powerful it is.
SUBSTRING()
accepts up to 3 parameters; 2 are required and 1 is optional. The 2 required parameters are target_string and start_position. The lone optional parameter is the length parameter.
If the 3rd optional length parameter is omitted, SUBSTRING()
returns the string portion from start_position to the end of target_string.
For this 1st example query, SUBSTRING()
starts at the 2nd character - specified by the 2 start_position parameter - and extracts 3 characters - denoted by the 3 length parameter:
mysql> SELECT SUBSTRING('Joshua', 2, 3) AS sub;
+------+
| sub |
+------+
| osh |
+------+
We can see that without the 3rd length parameter, the returned string is to the end of the target_string:
mysql> SELECT SUBSTRING('Joshua', 2) AS sub_no_len;
+------------+
| sub_no_len |
+------------+
| oshua |
+------------+
If start_position is negative, SUBSTRING()
counts from the end of target_string that many characters and returns either the remainder of target_string (if no length parameter is specified), or the specified number of characters dictated by length:
mysql> SELECT SUBSTRING('Joshua', -3) AS sub_neg_stt;
+-------------+
| sub_neg_stt |
+-------------+
| hua |
+-------------+
mysql> SELECT SUBSTRING('Joshua', -3, 2) AS sub_neg_stt_len;
+-----------------+
| sub_neg_stt_len |
+-----------------+
| hu |
+-----------------+
What do you think?
And there you have it. A high-level overview of the 5 MySQL String Functions You Should Know. I hope you enjoyed this month's featured article. Be sure and subscribe so you get the monthly featured article delivered directly to your inbox.
Once again, I have curated some fantastic reads for you this week. Let’s get to them!
This Week’s Picks
✔️ Why we should decouple business logic from WordPress
#wordpress
This is really a great read and does make sense in several aspects. I agree with making code as modular and reusable as possible and this is something I struggle with learning - and implementing - all of the time.
But, at some point, in order to solve the business's specific problem, shouldn't we have the business logic integrated? I believe that no 2 businesses are the same even though they may serve the same customers. Hence, custom development.
All in all, this is where a newbie dev like me learns from those more experienced.
✔️ 13 Best PHP Books in 2022 [Learn PHP ASAP]
#php
This is one mega-list of books but I've honestly not read any of them (as of the time of writing). I'm really surprised that my favorite book to learn PHP wasn't on the list. I'm definitely going to check out some that are mentioned though.
Have you read any of these? Feel free to share any of your suggestions with me.
✔️ What if … MySQL’s Repeatable Reads Cause You to Lose Money?
#mysql
I'm definitely bookmarking this blog post because it is that good. The Percona blog always has some incredible articles and this one is no different.
I personally have to study transactions and transaction isolation more in-depth. I know what they are and why we have them. But, I have to learn better how to use them for the requirements at hand. This particular blog post is the perfect one for study as it has plenty of examples with clear explanations and is well written. We all have so much to learn right?
✔️ How to Create a Custom Drupal 9 Theme in 9 Simple Steps
#drupal
I am not currently a Drupal user or developer so my knowledge of the platform is quite limited. In other words, I don't have anything intelligent to add lol. 😁
That said, this article lays out 9 easy to follow steps with screenshots and instructions for each so it is a solid written example for those interested in setting up the scaffolding for a custom theme.
✔️ Cleaning Up Your CodeIgniter Controllers
#codeigniter
Although my current day job is not (yet) that of a full-time web developer, I do spend about 1/3 of my time programming. I'm currently porting over an internal reporting dashboard I wrote in vanilla PHP to CodeIgniter 4 ( which I absolutely enjoy this framework!).
I'm taking steps to put into practice, more of the suggestions in this article; keeping the Controllers skinny and fattening up the Models (I do tend to use and make libraries a great deal also).
Also, in CI4, we can do validation in the Model so that in itself moves some of the code out of the Controller.
✔️ The Coding Languages of WordPress – PHP
#wordpress #php
WordPress as a product delivers a great deal of value to users. As a whole, WordPress is built of several technologies. PHP is just one of them (albeit an important one).
This high-level overview covers:
What PHP does
A brief history of PHP
Several of the popular frameworks using PHP
Further informational resources on PHP.
This post is Part 3 in a series of posts covering the languages of WordPress and they are fantastic reads as well so check them out also (internal links within the referenced article).
⭐⭐⭐ Many of the posts I curate and share in this newsletter, I read and enjoyed on Medium. If you are not yet and member and wish to join the platform, I would be more than grateful if you would use my sign-up link. At no extra cost to you, I will receive a small portion of your membership fee when you sign up. Thank you! 🙏
Extra stuff…
📣 I’ve subscribed to a fantastic newsletter that is really outside of my niche but provides a ton of value for me each week so I highly recommend that you check out The Curious Bunch.
🧰 Freelancers need many tools to make the wheels turn. This post has 28 listed that you can use.
📰 Get newsletter recommendations on topics that might not be in your niche by subscribing to this newsletter.
Did you enjoy this issue of OpenLamp.tech? Please share it with others so that they can enjoy the content as well. Thank you!!!
Please “Reply” and let me know what you think of this issue of OpenLamp.tech.
What do you want to see more of?!?
What do you want to see less of?!?
I’d love to know!
Also, share with me links to your published blogs and articles so that I can read them and possibly include them here in the newsletter!
Thank you for reading. I hope you have a great rest of your week.
Take care.
Joshua Otwell
Visit my blog Digital Owl’s Prose, where I write regularly on CodeIgniter, PHP, and SQL. I’m also active on Medium, LinkedIn, Twitter, and Instagram.
How can I help you?
Are you thinking of starting up a blog? I use WordPress for my blog, Digital Owl’s Prose. Let’s both save money on the plans offered. 💸
Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. ✉️
Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.
Want to support this newsletter and my work? Drop some spare change in my Tip Jar.💰
Disclosure: Some of the services and products links in this email are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.
Cover image attrition - Image by Gerd Altmann from Pixabay