SQL is used to manage data in relational databases. While it is most commonly associated with querying databases, SQL offers much more through its versatile functions that enable efficient data analysis and manipulation. A significant amount of raw data is in text form, so having versatile methods for manipulating strings is very important. Raw data is usually not available in the desired format. So to produce features that are usable, relevant and informative, we manipulate them.
Among the most important SQL tools are string functions, which are SQL functions that manipulate strings. We’ll look at five different methods to manipulate strings in this article. You can “edit” string data using a variety of SQL functions. You’ll discover how useful they are as you study SQL. From determining the length of a string to trimming extra spaces or characters, these functions empower SQL developers to increase the effectiveness of their code.
Knowing which SQL functions are used to manipulate strings is important for any SQL developer. This article will walk you through five various string methods in SQL to get you going. Let’s get started!
SQL String Functions
1. CONCAT Function
The SQL CONCAT function joins two or more strings together to create a single string. The syntax is as follows:
CONCAT(str1,str2,str3..);Code language: SQL (Structured Query Language) (sql)Strings are passed to the function as a list of comma-separated arguments to be concatenated. Let’s understand with the below example:
Example 1:
SELECT CONCAT('SQL CONCAT function', ' example');Code language: SQL (Structured Query Language) (sql)Output:
SQL CONCAT function exampleCode language: SQL (Structured Query Language) (sql)Here in the Concat function, we passed two strings “SQL CONCAT function” and “example” as arguments and we get a combined SQL string as a result. When presenting database information in a readable fashion, this approach is very helpful.
Example 2:
Let’s assume we have a table named Employee that stores the employee’s name, id, date of joining, and department. We want the details of employees and present them in the most readable way. And the best way to do this is shown below.
SELECT CONCAT('The employee details are:', 'id', 'firstname', ' lastname', 'dOj', 'department')
FROM Employee ;Code language: SQL (Structured Query Language) (sql)Output:
The employee details are: 005 John Doe 23-05-2022 SalesCode language: SQL (Structured Query Language) (sql)The string concatenation symbol “||” can be used in place of the CONCAT SQL string function in several database systems. Although this operator complies with SQL standards, not all databases support it, for instance, you must use “+” in SQL Server.
2. LOWER and UPPER Function
Each character in a string is changed to lowercase using the SQL LOWER function and use the UPPER function if you want to change every character in a string into uppercase. The syntax of LOWER and UPPER functions are:
LOWER(string);Code language: SQL (Structured Query Language) (sql)UPPER(string);Code language: SQL (Structured Query Language) (sql)The string that the LOWER function returns has all the characters in lowercase. If the input string is NULL, it returns NULL. And the UPPER function also returns NULL if the input string is NULL otherwise, it returns a new string with all the letters in uppercase.
Example: LOWER()
Let’s understand through the below simple example, this query uses the LOWER function to return the names of departments in lowercase.
SELECT
LOWER(depart_name)
FROM
Department
;Code language: SQL (Structured Query Language) (sql)Output:
finance
sales
accountsCode language: SQL (Structured Query Language) (sql)Let’s see an example of the UPPER function.
Example 1: UPPER()
SELECT UPPER('this sql function converts to uppercase');Code language: SQL (Structured Query Language) (sql)Output:
THIS SQL FUNCTION CONVERTS TO UPPERCASECode language: SQL (Structured Query Language) (sql)Let’s check another example.
Example 2: UPPER()
Here the below statement updates the emails of employees from lower to uppercase.
UPDATE employees
SET
emailofEmp = UPPER(emailofEmp);Code language: SQL (Structured Query Language) (sql)3. REPLACE Function
Sometimes you want to find a substring in a column and replace it with a new one. For example, you could want to convert a broken link to a new one or give an old product a new name. Here comes a very helpful string function called REPLACE. You can use the REPLACE function to replace each substring that appears in a string with a different substring. The syntax is as follows:
REPLACE(string, old_substring, replace_substring);Code language: SQL (Structured Query Language) (sql)The replace_substring will be substituted for every instance of the old_substring found by the REPLACE function.
Let’s see some examples.
Example 1:
Here we want to update a part of a record, it will be done as follows:
SELECT REPLACE('Hey Fun Function', 'Fun', 'Replace');Code language: SQL (Structured Query Language) (sql)Output:
Hey Replace FunctionCode language: SQL (Structured Query Language) (sql)Note: REPLACE function searches in a case-sensitive manner.
Let’s try a more practical example of using this function to manipulate strings now. Say you have a database called registration that contains the names of your employees. Suppose we have an employee named John and we need to change his last name from Lee to Doe. We can quickly update his record with the REPLACE function.
Example 2:
UPDATE registration
SET name = REPLACE(name, 'Lee', 'Doe')
WHERE name LIKE 'John%'Code language: SQL (Structured Query Language) (sql)4. TRIM Function
With the help of the TRIM function, you can remove leading and/or trailing characters from a string. The syntax is as follows:
TRIM([LEADING | TRAILING | BOTH] trim_character FROM source_string);Code language: SQL (Structured Query Language) (sql)The trim character that needs to be deleted must be specified first. If the TRIM function is not specified it will eliminate blank spaces from the source string. The source_string should then come after the FROM clause. Thirdly, the TRIM function specifies which side of the source_string the trim_character will be removed from using the LEADING, TRAILING, and BOTH parameters.
Let’s understand through practice example, the below statement removes both the leading and trailing # of the string.
Example 1:
SELECT TRIM('#' FROM '#2002#');Code language: SQL (Structured Query Language) (sql)Output:
2002Code language: SQL (Structured Query Language) (sql)Let’s see another example.
Example 2:
SELECT TRIM (TRAILING '2023' FROM 'SQL2023')
FROM Reg;Code language: SQL (Structured Query Language) (sql)Output:
"SQL"Code language: SQL (Structured Query Language) (sql)5. ASCII and CHR Function
The SQL ASCII function returns the ASCII code for the string’s leading character. This SQL string function will only return a value for the first character in a string of characters and ignore the rest. On the other hand, the CHR accepts an ASCII code and returns the corresponding character. Both ASCII and CHR are opposite SQL Functions. Both syntaxes are as follows:
ASCII(string)
CHR(ascii)Code language: SQL (Structured Query Language) (sql)In MySQL, 0 is returned by the ASCII function if the string is empty. In some other database systems, such as PostgreSQL, if the string is empty, it returns NULL. It should be noted that depending on how the RDMBS implements the function, the ASCII function may return an error if the input character is outside of the range (0,255). And in the case of CHR, the function returns NULL if the input number is NULL. Let’s see some examples:
The first character in the string, the letter A, is returned by the following statement’s ASCII code. And in the second example, the statement returns characters of the ASCII code 65.
Example: ASCII()
SELECT ASCII('ASCII function example');Code language: SQL (Structured Query Language) (sql)Output:
65Code language: SQL (Structured Query Language) (sql)Example: CHR()
SELECT CHR(65);Code language: SQL (Structured Query Language) (sql)Output:
ACode language: SQL (Structured Query Language) (sql)Conclusion
Textual information is a crucial component of data analysis. So, to handle strings, we need effective ways and methods. You can alter or update strings using the SQL functions mentioned in this article. Also, there are so many SQL functions present out there which can be used to perform various operations. To learn more about them click here.
Reference
https://stackoverflow.com/questions/39490344/string-manipulation-in-sql