DEV Community

loading...
Cover image for Popular string functions in MySQL - CONCAT and SUBSTRING

Popular string functions in MySQL - CONCAT and SUBSTRING

Jo
・2 min read

There are some very helpful and fun string functions in MySQl and I've decided to review them here as I go through Colt Steele's MySQL Udemy course. This article will serve as my notes for that section of the course.

CONCAT

The CONCAT function concatenates two or more value together. This is helpful for formatting data that is useful to the person reading the output.

select concat(author_fname, " ", author_lname, " - ", title) AS Author
from books;
Enter fullscreen mode Exit fullscreen mode

A variation of the CONCAT function is the CONCAT_WS function, which stands for CONCAT with separator. This is useful if you're separating multiple pieces of data with the same symbol between them. The first argument in the CONCAT_WS function is the operator used to separate all the the other individual arguments.

select concat_ws(' - ', author_fname, author_lname, title) 
AS Author
from books;
Enter fullscreen mode Exit fullscreen mode

As you can see from the example above, there is now a dash between the author's first name, last name, and book title.

SUBSTRING

Another helpful function is substring. This function is useful for extrapolating data when you only need a part of the string. This function is also useful in combination with other functions to make even more powerful queries. The basic makeup of a simple substring query includes the string that data needs to be extrapolated from, and the location of the data.

select substring('Jowayne', 1,2)
Enter fullscreen mode Exit fullscreen mode

The result of the example above would give me "Jo", as the Jowayne is the string to be analyzed, the 1 is location where I need the extrapolation to begin, and 2 is where I need it to end.
You can also choose to just use one number(the starting index) and the function will automatically start at that number and go till the end, like so:

select substring('Jowayne',3)

Enter fullscreen mode Exit fullscreen mode

The result of this would be "wayne" since I started at 3.
The substring function also accommodates negative numbers. The substring would begin at the end of the string so:

select substring('Jowayne',3)
Enter fullscreen mode Exit fullscreen mode

The result would be 'yne'.

And as I referenced before, you can use substring with other string functions:

select concat(substring(title, 1, 10),'...') from books;
Enter fullscreen mode Exit fullscreen mode

That's it for this post! Thanks for reading :)

Discussion (0)