The string functions in SQL play a vast role in data manipulation. In this article, we will explain all about the string functions in SQL, how to use them, best practices, and their benefits.
What are string functions in SQL?
The string functions are used for manipulating text data in SQL. These functions are very essential for data cleaning, formatting, and transformation tasks. Common string functions are as follows –
ASCII(), CHAR, LOWER(), UPPER(), LEFT(), RIGHT(), TRIM(), LTRIM(), RTRIM(), SUBSTRING(), STUFF(), SPACE(), REPLICATE(), REVERSE(), REPLACE(), QUOTENAME(), PATINDEX(), STR(), NCHAR(), CHARINDEX(), CONCAT(), CONCAT WITH +, CONCAT_WS(), LEN(), COL_LENGTH(), DATALENGTH(), FORMAT(), TRANSLATE(), UNICODE(), SOUNDEX(), DIFFERENCE() etc.
Basics of String Functions in SQL
SQL offers plenty of string functions that care about various needs of businesses as follows –
- SQL provides case functions like UPPER(), LOWER(), etc for manipulating texts.
- SQL provides pattern matching that allows searching for specific patterns within strings using operators and wildcards.
- The string functions in SQL provide trimming and padding like TRIM(), LTRIM(), and RTRIM(), etc that help in the cleanup of unwanted spaces.
- The replace function helps to replace a specific text within a string.
- The string conversion functions like CAST() and CONVERT() help to convert data types of data fields.
Common string functions in SQL
Refer below the common string functions in SQL-
ASCII Function
The ASCII Function is short form of American Standard Code for Information Interchange, returns the ASCII value of the first character.
Syntax
SELECT ASCII(character);
Example
SELECT 'Lal Mohammad' AS 'Data', ASCII('Lal Mohammad') AS ASCII_Value_Data;
CHAR Function
CHAR Function, returns the character based on the ASCII code.
Syntax
SELECT CHAR(code);
Example
SELECT '65' AS ASCIICode, CHAR(65) AS CodeToCharacter;
LOWER Function
LOWER Function, converts all characters in a string to lowercase.
Syntax
SELECT LOWER(text);
Example
SELECT LOWER('SQLPOST Academy is an Educational Website');
UPPER Function
UPPER Function, converts all characters in a string to uppercase.
Syntax
SELECT UPPER(text);
Example
SELECT UPPER('SQLPOST Academy is an Educational Website');
LEFT Function
LEFT Function, extracts the number of characters from a string or a specified string value columns starting from the left side.
Syntax
SELECT LEFT(string, number_of_chars);
Example
SELECT LEFT('SQLPOST Academy is an Educational Website',15) AS Left15Characters;
RIGHT Function
RIGHT Function, extracts the number of characters from a string or a specified string value columns starting from the right side.
Syntax
SELECT RIGHT(string, number_of_chars);
Example
SELECT RIGHT('SQLPOST Academy is an Educational Website',19) AS Right19Characters;
TRIM Function
TRIM Function, removes all types of spaces including leading and trailing or other specified characters from a string.
Note: The TRIM function is introduced from the Microsoft SQL Server 2017 version, So please avoid using it in its earlier versions as it will not work.
Syntax
SELECT TRIM([characters FROM ]string);
Example
SELECT TRIM('#! ' FROM ' #SQLPOST Academy! %^&*') AS TrimmedString;
In the above query, the TRIM function is used to remove all types of spaces including leading and trailing or other specified characters from a string.
LTRIM Function
LTRIM Function, removes all leading spaces from a string or a specified string column.
Syntax
SELECT LTRIM(string);
Example
SELECT LTRIM(' SQLPOST Academy! ') AS LTrimmedString;
In the above query, the LTRIM function is used to remove all leading spaces from a string.
RTRIM Function
RTRIM Function, removes all trailing spaces from a string or a specified string column.
Syntax
SELECT RTRIM(string);
Example
SELECT RTRIM(' SQLPOST Academy! ') AS RTrimmedString;
In the above query, the RTRIM function is used to remove all trailing spaces from a string.
SUBSTRING Function in SQL
SUBSTRING Function, extracts some characters from a string or a specified string column.
Syntax
SELECT SUBSTRING(string, start, length);
Example
SELECT SUBSTRING('SQLPOST Academy is an Educational Website', 1, 15) AS ExtractSubString;
STUFF Function
STUFF Function, inserts the string at a given position and removes the number of characters specified from the original string.
Syntax
SELECT STUFF(string, start, length, new_string);
Example
SELECT STUFF('SQLPOST Academy', 1, 7, 'SQLPOST Academy') AS ExtractStuff;
--OR
SELECT EmployeeName, STUFF(EmployeeName,1,2,'SQLPOST')
AS EmployeeNameStuff FROM Employee;
--OR
SELECT FirstName, STUFF(FirstName,1,2,UPPER(LEFT(FirstName,2)))
AS EmployeeNameStuff FROM t_Employees;
--OR
DECLARE @String1 VARCHAR(20) = 'Microsoft Server'
DECLARE @String2 VARCHAR(20) = 'SQL Server 2012'
SELECT @String1 + ' -> ' + STUFF(@String1, 11, 0, 'SQL ')
AS 'String 1',
@String2 + ' -> ' + STUFF(@String2, 15, 1, '8 R2')
AS 'String 2';
In the above query, the STUFF function is used to insert the string at a given position and remove the number of characters specified from the original string.
SPACE Function in SQL
SPACE Function, returns a string of the specified number of space characters or a specified string column.
Syntax
SELECT SPACE(number);
Example
SELECT SPACE(10);
--Or
SELECT FirstName +SPACE(2)+LastName FROM t_Employees WHERE Salary >='15000';
REPLICATE Function
REPLICATE Function, repeats a string or a specified string column on a specified number of times.
Syntax
SELECT REPLICATE(string, integer);
Example
SELECT REPLICATE('SQLPOST Academy',3) AS ExtractReplicate;
--Or
SELECT FirstName, REPLICATE(FirstName,4) AS ExtractReplicate
FROM t_Employees WHERE Salary >='15000';
REVERSE Function
REVERSE Function, reverse a string or a specified string column in the result.
Syntax
SELECT REVERSE(string);
Example
SELECT REVERSE('SQLPOST Academy') AS ExtractReverse;
--Or
SELECT FirstName, REVERSE(FirstName) AS ExtractReverse
FROM t_Employees WHERE Salary >='15000';
REPLACE Function
REPLACE Function, replaces all occurrences of a substring with a new string within a string or a specified string column in the result.
Syntax
SELECT REPLACE(string, old_string, new_string);
Example
SELECT REPLACE('SQL Academy','L','LPOST') AS ExtractReplace;
--Or
SELECT FirstName, REPLACE(FirstName,'n','n Sahab') AS ExtractReplace
FROM t_Employees WHERE Salary >='15000';
QUOTENAME Function
QUOTENAME Function, returns a string with delimiters added to make the string a valid delimited identifier in the result.
Syntax
SELECT QUOTENAME(string, quote_char);
Example
SELECT QUOTENAME('abcdef','""') AS ExtractQuotename;
--Or
SELECT FirstName, QUOTENAME(FirstName,'') AS ExtractQuotename
FROM t_Employees WHERE Salary >='15000';
PATINDEX Function
PATINDEX Function, returns the position of a search pattern from a string or a specified string column in the result.
Syntax
SELECT PATINDEX(%pattern%, string);
Example
SELECT PATINDEX('%post%', 'sqlpost.com') AS ExtractPatIndex;
--OR
SELECT FirstName, PATINDEX('%t%',FirstName) AS ExtractPatIndex
FROM t_Employees WHERE Salary >='15000';
STR Function
STR Function, returns a number or a specified number column as a string in the result.
Syntax
SELECT STR(number);
Example
SELECT STR(185) AS ExtractString;
--OR
SELECT FirstName,Salary, STR(Salary) AS ExtractString
FROM t_Employees WHERE Salary >='15000';
NCHAR Function
NCHAR Function, returns a Unicode character based on the number code or a specified number column in the result.
Syntax
SELECT NCHAR(number_code);
Example
SELECT NCHAR(185) AS ExtractNcharNumberCodeToUnicode;
--OR
SELECT FirstName,Salary, NCHAR(Salary) AS ExtractNcharNumberCodeToUnicode
FROM t_Employees WHERE Salary >='15000';
In the above query, the NCHAR function is used to return a Unicode character based on the number code in the result.
CHARINDEX Function
CHARINDEX Function, returns the position of a substring from a given string or a specified string column in the result.
Syntax
SELECT CHARINDEX(substring, string);
Example
SELECT CHARINDEX('c', 'SQL Academy') AS MatchPositionCharIndex;
--OR
SELECT FirstName,CHARINDEX('t',FirstName) AS MatchPositionCharIndex
FROM t_Employees WHERE Salary >='15000';
CONCAT Function
CONCAT Function, Combines two or more strings into one string.
Syntax
SELECT CONCAT(string1, string2, …., string_n);
Example
SELECT CONCAT('SQL Academy',' ','is a',' ','Educational Website') AS ExtractsConcat;
--OR
SELECT CONCAT(FirstName,' ',LastName,' ',Salary) AS ExtractsConcat
FROM t_Employees WHERE Salary >='15000';
CONCAT WITH + Function
CONCAT WITH + Function, Combines two or more strings into one string using the plus(+) sign excluding the ‘CONCAT’ word.
Syntax
SELECT string1 + string2 + string_n;
Example
SELECT 'SQL Academy'+' '+'is a'+' '+'Educational Website' AS ExtractsConcatPlus;
--OR
SELECT FirstName+' '+LastName AS ExtractsConcatPlus
FROM t_Employees WHERE Salary >='15000';
CONCAT_WS Function
CONCAT_WS Function, Combines two or more strings into one string along with a separator.
Note: This function is introduced by Microsoft SQL Server 2017 and above versions. So please don’t try to use it in its earlier versions.
Syntax
SELECT CONCAT_WS(separator, string1, string2, …., string_n);
Example
SELECT CONCAT_WS('.','www','sqlpost','com') AS ExtractsConcatWS;
--OR
SELECT CONCAT_WS(' ',FirstName,LastName) AS ExtractsConcatWS
FROM t_Employees WHERE Salary >='15000';
In the above query, the CONCAT_WS function is used to Combine two or more strings into one string along with a separator.
LEN Function
The LEN Function is used to find the length of a string. It’s particularly useful when you need to find the number of characters in a text field.
Syntax
SELECT LEN(string);
Example
SELECT LEN('www.sqlpost.com') AS ExtractsLengthOfString;
--OR
SELECT LEN(FirstName+LastName) AS ExtractsLengthOfString
FROM t_Employees WHERE Salary >='15000';
COL_LENGTH Function
COL_LENGTH Function, helps to find the defined length of a column of a database object like a table, views, etc.
Syntax
SELECT COL_LENGTH(object_name, column_name);
Example
SELECT COL_LENGTH('Employee','EmployeeName') AS ColLenghtOfEmployee;
DATALENGTH String Function in SQL
DATELENGTH Function, helps to find the number of bytes used to represent an expression or datalength.
Syntax
SELECT DATALENGTH(expression);
Example
SELECT DATALENGTH('www.sqlpost.com') AS ExtractsDataLength;
--OR
SELECT DATALENGTH(FirstName) AS ExtractsDataLength
FROM t_Employees WHERE Salary >='15000';
FORMAT String Function in SQL
FORMAT Function, helps to format a value with the specified format.
Syntax
SELECT FORMAT(value, format);
Example
SELECT FORMAT(GETDATE(),'d','en-US') AS SlashFormat;
SELECT FORMAT(GETDATE(),'d','no') AS DotFormat;
SELECT FORMAT(GETDATE(),'d','zu') AS SlashFormat;
SELECT FORMAT(123456789, '##-##-#####');
SELECT FORMAT(123456789, '###-##-####');
SELECT FORMAT(123456789, '##-##-##-###');
TRANSLATE String Function in SQL
TRANSLATE Function, helps to find the string from the first argument after the characters specified in the second argument are translated into the characters provided in the third argument.
Note: This function is introduced by Microsoft SQL Server 2017 and above versions. So please don’t try to use it in its earlier versions.
Syntax
SELECT TRANSLATE(string, characters, translations);
Example
SELECT TRANSLATE('Monday', 'Monday', 'Sunday') AS ResultsInSunday;
UNICODE String Function in SQL
UNICODE Function, helps to find the Unicode value of the first character of an input expression or a specified string column.
Syntax
SELECT UNICODE(character_expression);
Example
SELECT UNICODE('SQL Academy') AS ExtractUnicode;
SOUNDEX String Function in SQL
SOUNDEX Function, helps to find a four-digit code to evaluate the similarity of two strings or two specified string columns.
Syntax
SELECT SOUNDEX(expression);
Example
SELECT SOUNDEX('Fair') AS Extracts1stSoundex,
SOUNDEX('Fairy') AS Extracts2ndSoundex;
--OR
SELECT SOUNDEX(FirstName) AS SoundexFirstName,
SOUNDEX(LastName) AS SoundexLastName FROM t_Employees;
DIFFERENCE String Function in SQL
DIFFERENCE Function, helps to compare two Soundex values and returns an integer value.
Syntax
SELECT DIFFERENCE(expression, expression);
Example
SELECT DIFFERENCE('Fair','Fairy') AS ExtractsDifferenceSoundex;
--OR
SELECT FirstName,LastName, DIFFERENCE(FirstName,LastName)
AS ExtractsDifferenceSoundex FROM t_Employees;
Disadvantages of Using String Functions in SQL
While string functions in SQL are very powerful but they can impact on query performance. It is advisable to avoid extensive string manipulation in WHERE clauses that helps optimal database performance. Also, avoid misuses of wildcards and ignoring case sensivity.
Best Practices for String Functions in SQL
To use the full potential of string functions it is advised to adopt best practices. This involves selecting the appropriate function for the task at hand, handling NULL values with care, and being mindful of the syntax variations across different database systems. By ensuring your SQL queries stands the test of time, enhancing code readability and improving efficiency.
FAQs
String Functions play a vast role in maintaining data quality by facilitating accurate data manipulation. From cleaning up messy data to standardizing formats, these functions contribute to a more reliable database.
It depends on the specific function. Functions like UPPER() and LOWER() are case-sensitive, while others may not be.
Regular expressions and stored procedures are alternative methods for text manipulation, depending on the database system.
String functions can affect performance, especially when applied to large datasets. It’s crucial to use them judiciously and follow best practices.
Yes, you can nest multiple string functions to achieve more complex text manipulations. However, be mindful of performance implications.
Yes, most relational database management systems (RDBMS) support a standard set of string functions, although some variations may exist.
While String Functions are primarily designed for text manipulation, some, like CAST and CONVERT, allow limited manipulation of numeric data. However, it’s advisable to use dedicated numeric functions for optimal results.
Conclusion
In conclusion, mastering string functions in SQL is a journey that opens up a world of possibilities in data manipulation. From the basics to advanced techniques, a solid understanding of these functions empowers database professionals to extract valuable insights.
We hope you like this article very well. Please share it on your social media network, which may be beneficial for others. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response ASAP.
