SQL Data Interview Series: String Functions in SQL
Hey there everyone. Welcome to Day 6 of the SQL Data Analyst Interview Questions series. You can read up yesterday’s topic — Stored Procedures in SQL — here. Today, we’ll be talking on String Functions in SQL.
“What are String Functions?”, you ask. First off, let’s find them in SSMS. On the left-hand side of your SSMS screen, you’ll see a list of databases. Now click on the + box, and you’ll see a dropdown of objects present within that database. Click on ‘Programmability’. You’ll see ‘Stored Procedures’ and ‘Functions’. Under ‘Functions’, click on the ‘System Functions’ and you’ll see a list of system functions e.g. aggregate functions like AVG(), COUNT(), SUM(); date and time functions like GETDATE(), MONTH(), YEAR(); mathematical functions like ABS(), CEILING(), FLOOR(); and string functions. We’ll be discussing some of these string functions today.
String functions are the predefined functions that allow the database users for string manipulation. They are used to perform an operation on input string and return an output string. These functions only accept, process, and give results of the string data type. We’ll discuss some of these today.
- LEFT(): This returns the left-most specified number of characters from a character expression. For this, you input the expression you want this function to be passed on, then the number of characters you want returned. Let’s use the ‘Encounters’ table from our SQL for Healthcare project. From the ‘Organization’ column, we want the first 8 characters of each row.
2. RIGHT(): This returns the specified number of characters from the right part of a character expression. Its syntax is similar to that of LEFT(). Now, let’s get the last 8 characters of each row.
3. LTRIM(): This returns a character expression after removing leading blanks, i.e. it gets rid of the spaces on the left-hand side of a character expression. For instance:
It removes the extra space at the beginning of the name.
4. RTRIM(): This returns a character expression after truncating all trailing blanks. This is similar to LTRIM(), only that while LTRIM() is for the left-hand side, RTRIM() is for the right-hand side.
5. LEN(): This returns the number of characters in the given string expression. It is used to find out how many characters are in a string.
As we can see, there are 19 characters in the string ‘Healthcare_Expenses’.
6. REPLACE(): This replaces all occurrences of the second expression in the first expression with a third expression. E.g. We have a string ‘Youthful’ which we want to change to ‘Young’, i.e. changing the ‘thful’ to ‘ng’. We’ll use the REPLACE() string to replace ‘thful’ with ‘ng’.
1st expression to carry out the change on- ‘Youthful’; 2nd expression to change- ‘thful’; 3rd expression to be changed to- ‘ng’.
7. UPPER(): This returns a character expression with lowercase letters converted to uppercase.
8. LOWER(): The direct opposite of UPPER(), this returns a character expression with uppercase letters converted to lowercase.
That’s it today on String Functions in SQL. As always, leave lots of claps and encouraging comments. While we were able to discuss eight of these, there are so much more string functions to learn, and these could come in handy especially during Data Cleaning in SQL. Tomorrow, we’ll be rounding off our SQL Series with the topic- ‘Joins in SQL’, as this was requested by a reader. Hope to see you here 🤗.
