Day 21: String Functions In SQL

Day 21 Of Learning

Image By Author

Hey Guys, hope you are good.

Ready for learning?

Today, we’ll be looking into string functions in SQL specifically MySQL. It isnt complex and they’re just basic functions you’ll be using in your day to day SQL.

So, let’s get started.

What Are String Functions?

String functions are some functions in SQL that apply to strings for multiple purposes. It maybe to calculate the memory taken up by a string, or to check how many characters there are in a string, or even to join two strings. A few examples of string functions are, CONCAT, LENGTH, CHAR_LENGTH, UPPER, LOWER, SUBSTRING, LIKE, REPLACE etc.

However, I’ll be discussing the most common ones used in MySQL below as there are 30+ string functions if you combine all the DBs.

Common String Functions In MySQL

Here are 10 common string functions in MySQL:

1. LENGTH & CHARACTER COUNT

   • LENGTH(string): Returns the length of a string in bytes.

   • CHAR_LENGTH(string): Returns the number of characters in a string.

2. CONCATENATION

   • CONCAT(string1, string2, ...): Combines multiple strings.

   • CONCAT_WS(separator, string1, string2, ...): Concatenates with a separator.

3. CASE CONVERSION

   • UPPER(string): Converts to uppercase.

   • LOWER(string): Converts to lowercase.

4. SUBSTRING EXTRACTION

   • SUBSTRING(string, start, length): Extracts part of a string.

   • LEFT(string, n): Returns the leftmost n characters.

   • RIGHT(string, n): Returns the rightmost n characters.

5. TRIMMING SPACES

   • TRIM(string): Removes leading & trailing spaces.

   • LTRIM(string): Removes leading spaces.

   • RTRIM(string): Removes trailing spaces.

6. SEARCHING WITHIN A STRING

   • INSTR(string, substring): Returns position of substring.

   • LOCATE(substring, string): Same as INSTR.

7. STRING REPLACEMENT

   • REPLACE(string, old_substring, new_substring): Replaces occurrences of a substring.

8. STRING REVERSAL

   • REVERSE(string): Reverses a string.

9. STRING COMPARISON

   • STRCMP(string1, string2): Compares two strings (0 if equal, -1 if string1 < string2, 1 if string1 > string2).

10. PATTERN MATCHING

    • LIKE → Used for pattern matching with % and _.

I dont know where you work and how much depth is required in your role or if you’re finding your first job, but according to me LIKE, LENGTH, CONCAT, CHAR_LENGTH, TRIM, SUBSTRING, UPPER, LOWER will probably be the functions you use almost daily.

Exercise

1. Use the above common functions that I have made bold in your code

2. Use them on different strings or 1 string that is upto you

Hey Guys, I put in a great effort to provide you with guidance. I put out free content for your learning. If you’re willing you can buy me a coffee but its totally up to you, no compulsion.

---

I am a BI Engineer working at a Data And AI Firm. I’m trying to help newbies in Data land their first data job and impact as many fellow data professionals as I can. Here’s my contact info:

1. ajmal7809@gmail.com

2. Linkedin

3. bideveloper_ (Discord)