Mastering MySQL String Functions: A Comprehensive Guide

Name: String Functions in MySQL | Intermediate MySQL Series
Uploaded: 2026-01-13T23:10:15.039860+00:00
Channel: Alex The Analyst
Description: Summary and key takeaways on Mastering MySQL String Functions: A Comprehensive Guide, covering Introduction MySQL provides a rich set of built‑in string

Alex The Analyst

Jan 13, 2026

•

3 min read

YouTube video ID: KRXSJb9ql1Y

Source: YouTube video by Alex The Analyst — Watch original video

PDF

Introduction

MySQL provides a rich set of built‑in string functions that simplify working with textual data. This article walks through the most commonly used functions, illustrates their syntax, and shares practical use‑cases drawn from real‑world data‑cleaning tasks.

LENGTH – Measuring String Size

Syntax: SELECT LENGTH('your_string');
Returns the number of characters in the given string.
Example: SELECT LENGTH('Skyfall'); → 7.
Practical use‑case: Verify that phone numbers contain exactly 10 characters; flag any rows where LENGTH(phone_number) <> 10 for cleaning.

UPPER and LOWER – Standardizing Case

UPPER converts all characters to uppercase: SELECT UPPER(first_name) FROM employees;
LOWER converts all characters to lowercase: SELECT LOWER(first_name) FROM employees;
Why it matters: Data entry may be inconsistent (e.g., "Tom" vs "tom"). Converting to a single case helps enforce uniformity before further processing or comparison.

TRIM, LTRIM, RTRIM – Removing Unwanted Spaces

TRIM(string) removes leading and trailing whitespace.
LTRIM(string) removes only leading spaces.
RTRIM(string) removes only trailing spaces.
Example: SELECT TRIM(' sky '); → 'sky'.
Use‑case: Clean up user‑entered text fields where extra spaces cause mismatches in joins or filters.

LEFT and RIGHT – Extracting Fixed‑Length Segments

LEFT(string, n) returns the leftmost n characters.
RIGHT(string, n) returns the rightmost n characters.
Example: SELECT LEFT(first_name, 4) FROM employees; yields the first four letters of each name.
When to use: Quickly obtain prefixes or suffixes, such as extracting area codes from phone numbers.

SUBSTRING – Flexible Sub‑String Extraction

Syntax: SUBSTRING(string, start_position, length)
start_position is 1‑based; length is optional (returns the rest of the string if omitted).
Example: SELECT SUBSTRING(first_name, 3, 2) FROM employees; → returns characters at positions 3‑4 (e.g., "Leslie" → "SL").
Real‑world scenario: Pull the month from a YYYY-MM-DD date stored as a string: SUBSTRING(birth_date, 6, 2) yields the month component.

REPLACE – Substituting Characters or Sub‑Strings

Syntax: REPLACE(string, from_substring, to_substring)
Replaces every occurrence of from_substring with to_substring.
Example: SELECT REPLACE(first_name, 'a', 'z') FROM employees; changes all lower‑case "a" to "z".
Use‑case: Correct systematic typos or mask sensitive characters.

LOCATE – Finding the Position of a Sub‑String

Syntax: LOCATE(substring, string)
Returns the 1‑based index of the first occurrence of substring within string, or 0 if not found.
Example: SELECT LOCATE('x', 'Alexander'); → 4.
Application: Identify rows containing a specific pattern (e.g., names containing "an") and filter based on the returned position.

CONCAT – Merging Multiple Columns

Syntax: CONCAT(str1, str2, ..., strN)
Concatenates the supplied strings into a single result.
Example: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
Why it’s essential: Frequently needed to build full names, addresses, or composite keys from separate columns.

Putting It All Together

By combining these functions, you can perform sophisticated text transformations in a single query. For instance, to create a clean, title‑cased full name and extract the birth month, you might write:

SELECT
  CONCAT(UPPER(LEFT(first_name,1)), LOWER(SUBSTRING(first_name,2))) AS clean_first,
  CONCAT(UPPER(LEFT(last_name,1)), LOWER(SUBSTRING(last_name,2))) AS clean_last,
  CONCAT(clean_first, ' ', clean_last) AS full_name,
  SUBSTRING(birth_date,6,2) AS birth_month
FROM employees;

This demonstrates how length checks, case conversion, trimming, substring extraction, and concatenation can be chained to produce polished results.

Additional Resources

The full course also covers numeric functions, date‑time functions, and data‑type conversions, all part of the broader MySQL Functions module.

String functions are indispensable tools for cleaning, standardizing, and reshaping textual data in MySQL. Mastering them—especially LENGTH, UPPER/LOWER, TRIM, LEFT/RIGHT, SUBSTRING, REPLACE, LOCATE, and CONCAT—enables you to write concise queries that replace manual data‑wrangling, improve data quality, and streamline downstream analysis.

Frequently Asked Questions

Who is Alex The Analyst on YouTube?

Alex The Analyst is a YouTube channel that publishes videos on a range of topics. Browse more summaries from this channel below.

Does this page include the full transcript of the video?

Yes, the full transcript for this video is available on this page. Click 'Show transcript' in the sidebar to read it.

Summarize another video

Full Transcript YouTube

hello everybody in this lesson we're
going to be taking a look at string
functions now string functions are
built-in functions within MySQL that
will help us use strings and work with
strings differently now we're going to
look at a ton of different ones they all
have different use cases but I'll try to
walk through some of those as we go
along but we'll look at a lot of
different string functions in this
lesson we'll start off with one that's
really simple this one's called length
so if we select and then we say length
and let's say we put in
and I don't know why it's popping up
like that let's say we put in something
like sky or Skyfall or really anything
if we run this it's going to give us the
length of how long this string is so if
I come down here let me say select
everything from employee underscore
demographics and let's add a semicolon
here and let's run this one right down
now what we can do is we can look at how
long each person's name is what we can
do is just take the first name but then
we'll also do the length of the first
underscore name
so if we run this
now we get Leslie Tom Jerry Donna and it
gives us the length of their name if we
wanted to we could even order by this so
we could do order by and we could just
do two for now
and we can order by the length from the
shortest name all the way to the longest
name now one use case that I've used
length for in my actual job was when I
was working with phone numbers I wanted
to make sure that they were exactly 10
characters long otherwise something went
wrong somewhere in the data cleaning
process so I would go and look at the
length and I would make sure they're all
10 if any were above 10 I would go and
specifically look at those and try to
clean those and fix those up now let's
go on to the next one and these next
ones are pretty simple ones at least I
think they're fairly simple we're going
to look at upper first and it's doing
the same thing as the other one we'll do
upper and let's say we're going to do
Sky if we select upper Sky it's going to
give us an all uppercase or we can copy
this and we can do
lower
so now let me add semicolons otherwise
it's going to drive me crazy
uh let's try this lower now
it's going to do all lower even if I
make it all capital so if I say all
capital Sky it's going to make it all
lower so if we come back up
let's copy this
and instead of doing the length now
we'll do upper
let's go ahead and select this
so we have Leslie and then we have the
upper first name so upper all case
Leslie now this is actually really good
this is really helpful especially with
standardization is what I found a great
use case for it because sometimes it'll
be all capital Tom and sometimes I'll
put it in as T lowercase om uh and just
making them all uppercase are all
lowercase can help correct those really
simple standardization issues within a
single column the next one that we're
going to look at is trim now there's
multiple trims we have trim left trim
and right trim trim is basically going
to take the white space on the front or
the end and get rid of it which is
really really helpful so what we're
going to do is we're going to come right
here I'm going to say select and we'll
start off with trim
and let me add a semicolon every time so
then we'll do trim and for our actual
string we'll do something a little bit
odd we'll do some spaces and then we'll
do sky and then we'll add some spaces
let's run this and add our semicolon
that's going to be the end of me in this
lesson I was just adding semicolons now
it fixes it completely now what if we
don't add sky at all we'll just keep it
like this
but you can see that there's spaces
before and there's spaces after but
that's what trim does trims gets rid of
the leading and the trailing white
spaces now if we come up here and we
just do the left trim it's only going to
remove from the left hand side so we're
only getting rid of the left hand side
white spaces this right hand side as you
can see is really long it's still there
and if we do our trim we go ahead and
run this one it gets rid of the white
space on this side but it doesn't get
rid of the left space on this side now
let's keep going we have a lot to cover
still we're going to move on to what I
think is probably my most favorite
string function if I'm allowed to have a
favorite string function and that's
substring but I'm going to kind of work
us into substring a little bit by
looking at two smaller functions which
is left and right so let's select
everything and we'll do that from the
employee demographics again it's at our
semicolon now I'm going to run this now
I want to get the first name
and I'm going to do left of the first
underscore name just like this now when
you're using this is actually going to
be an error let's see if I highlight
over this it'll tell me what the error
is it says the parentheses is not a
valid position they're expecting
something else and basically what
they're telling us is that this is not
how it should be written we're looking
for a different value that different
value is actually a number we're going
to do comma and let's do four that's
what it was looking for it didn't want
this at the end it needed this comma 4.
and what we're actually specifying is
how many characters from the left hand
side do we want to select so we're
selecting the first name and we're going
from the left four characters let's go
ahead and run this
and so we have Leslie Tom April all the
way down you can see that there's only
four characters in each one so someone
like Chris the S is no longer going to
be there because we're only looking at
the first four characters now we can do
the exact same thing and let's actually
copy this uh down here so we'll come
we'll go like this
I make this a little more professional
and we'll do right so now we'll do write
if we do the right four it's going to go
from the right hand side of the string
and go left four so we're looking at the
far four most right characters now this
can be useful in certain instances but
if I'm being honest I don't use these
that much for the most part I'm pretty
addicted to using substrings I love
substrings I think they're fantastic and
let's look at substrings like this so a
substring is going to allow us to do a
few different things
let's do first underscore name the
second thing that we put within this
function is the position that we want to
start at so let's say we want to start
at the third position and then we
specify how many characters we want to
go so with this we specified four but
let's just do two so now we're going to
the third position and we're going over
to the right two characters let's go
ahead and run this so with Leslie we get
SL so we go one two and three we start
at the third position and then we take
two characters the S and the L I have
found this one to be extremely extremely
useful let's take this for example let's
do comma let's do birth underscore date
and let's run this I'm keeping
everything in here although it might be
a bit much let's say we have this birth
date and this middle column is the month
and we're running some you know query we
want to find the month that everyone is
born so we can do that very easily using
substring and we wouldn't have been able
to do this very easily using left or
right so now we're going to take this
birth date we're going to use this
substring and we want to select these
middle characters so what we need to do
since it's all standardized we do one
two three four five six we start
position six and we want to select one
and two let's go ahead and run this
and now we've pulled out all of the
months so we can say as birth underscore
month
and now we could save that put into a
temp table add it as a new column in our
table whatever we want to do but now we
have this information that we
desperately desperately wanted to know
so that is left right and substring
again substring is it's fantastic now
let's keep going the next thing that
we're going to take a look at is replace
now replace will replace specific
characters with a different character
that you want so let's actually copy all
this right here because I don't want to
keep writing this out
and we'll say select everything
now what we're going to do is we're
going to take the first underscore name
and then we're going to say replace and
then we'll also do the first underscore
name but we can specify what we want to
replace and then what we want to replace
it with so we have two more parameters
that we need to put in this function so
let's say a and let's replace it with a
z
let's just see what that does
let's go ahead and run this and so now
when we see the letter a and we are
specifying a lowercase a like Mark that
is replaced with a z so that's really
all replace does specifies what you want
to replace and then what you're going to
replace it with now let's take a look at
the next one and we're going to take a
look at a function called locate so if I
say select and let's do locate I'm going
to give it a string I'll say Alexander
that's my name and I'm going to specify
what I'm looking for so let's close this
parentheses the string that we're
actually looking for comes first so what
we're going to do is I'm looking for the
letter X in my name so we'll do X and
Alexander let's go ahead and run this
and it tells us that it is in position
four so we have one two three and four
that's where our position is that's
where it locates that sequence that
we're looking for now if we pull this
down here
place this right here and we'll change
this locate now let's say we're still
looking at the first name but we want to
locate people that have an a n
like this in their name let's go ahead
and run this
and we get zeros for everybody except
for and Andy so this might be something
where we put it into a CTE or a temp
table then we can filter Down based off
of these results towards only equals one
now the last one that we're going to
take a look at and let's go right here
we're going to first name last
underscore name now this one is super
super useful because what we can do is
have a concatenation of multiple columns
so let's go down right here so we have
first name and last name but if we come
down and we say concat we can then
combine these columns into one single
column so we'll do concat it'll do first
underscore name and then comma last
underscore name and if we run this
it's going to be Leslie and nope
combined into Leslie nope now this
doesn't look perfect right we don't want
it to look like that all we have to do
is come in here and we could do a little
space there's a lot of space in there
and if we run that now we have Leslie
and nope and we can call this as full
underscore name
and this is something that I've done a
million times in my real job where
there's multiple columns we want to
create one column out of it or take two
columns and create one column happens
all the time so this concat is really
really helpful to combine those columns
really quickly so that is all we're
going to take a look at in this string
functions lesson in my full course you
can find in the description below I also
have lessons on numeric functions date
and time functions converting different
data types all in the functions module
foreign
[Music]