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.
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;
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;
As you can see from the example above, there is now a dash between the author's first name, last name, and book title.
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)
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:
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:
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;
That's it for this post! Thanks for reading :)