SQL String Functions
Introduction to SQL Server’s String Functions
The built in SQL String functions make it possible for you to find and alter text values, such as VARCHAR and CHAR datatypes, in SQLServer. Using these functions you can alter a text value such as changing “Smith, Joe” to “Joe Smith.”
The built in SQL String functions make it possible for you to find and alter text values, such as VARCHAR and CHAR datatypes, in SQLServer. Using these functions you can alter a text value such as changing “Smith, Joe” to “Joe Smith.”
If you not familiar with SQL functions, then I would recommend staring with the Introduction to SQL Server Built-In Functions.
To get the most of this and our other lessons be sure to practice using the examples!
These functions are used to manipulate or return information about text expression such as CHAR and VARCHAR datatypes.
There are many string functions at your disposal. I would generally categorize them as:
Position
Transformation
Character Set
Soundex
All of the functions are listed on the String Functions (Transact-SQL) page. I would recommend visiting that page to learn about each function.
In the following table I categorized the functions and color coded them. The color code corresponds to the likely hood you would use that particular function in a business environment. Green are most likely to be used, and red less.
This isn’t a strict scale, and all functions have a use in some business case, but I wanted a way to help you winnow down the field to those most relevant.
Here is my attempt:
Latest Posts
-
Concatenate Text with SQL and how to protect from NULL.
In this article I’ll show you three ways you can use SQL to concatenate columns into a single expression. You’ll see as we go through this exercise that CONCAT_WS comes out on top for handling NULL the best! The examples are based on the PizzaDB, git it here! The goal is to combine the FirstName…
-
SQL LOWER Function (Transact SQL)
The SQL LOWER function converts the input character data to a lower case. Description The LOWER function takes character expression as an input and converts it into a lower case text. It helps to perform a case-insensitive search in the database. The SQL search is case-sensitive. The value Lower and lower are different. The Lower…
-
SQL UPPER Function (Transact SQL)
The SQL UPPER function converts the input character expression to an upper case and returns it. Description The UPPER function reads the input expression and converts lowercase characters to uppercase. SQL UPPER Function Usage Notes The character expression is the only argument for the UPPER function. It is the text to be converted to an…
-
SQL PATINDEX Function (Transact SQL)
The SQL PATINDEX function searches an input expression for a given pattern. It returns the starting index of the first occurrence of the pattern found in the input expression. Description The PATINDEX function is very similar to the LIKE function in SQL. However, it returns the starting position of the first occurrence of the pattern…
-
SQL RTRIM Function (Transact SQL)
The SQL RTRIM function trims all the trailing spaces from the right side of the input character expression. Description The RTRIM function returns a character expression after removing all the blank spaces from the right side. SQL RTRIM Usage Notes The only argument to the RTRIM function is the character expression that is to be…
-
SQL LTRIM Function (Transact SQL)
The SQL LTRIM function removes the leading blank spaces from the input character expression. Description The LTRIM function returns a character expression after removing all the blank spaces from the left side of the input expression. SQL LTRIM Usage Notes The input expression should be explicitly convertible to a varchar value. It can be a…
-
SQL CHARINDEX Function (Transact SQL)
The SQL CHARINDEX function returns the starting position of a character expression in another character expression. Description The CHARINDEX function helps with string data manipulation like some other SQL functions of SUBSTRING, REPLACE, CONCAT, etc. The CHARINDEX function finds the beginning of a character expression in a given character expression. The result of the CHARINDEX…
-
SQL REPLACE Function (Transact SQL)
SQL REPLACE function evaluates an input string for a specified substring pattern and replaces these substrings with another specified string value. Use it to substitute one set of characters for another with a character based value. Description The REPLACE function’s purpose is string manipulation. It replaces string patterns within a string with a new string.…
-
SQL SUBSTRING Function (Transact SQL)
Description The SQL SUBSTRING function extracts a part of input expression or a ‘substring’ and returns it. The starting position and the substring’s length are passed as arguments of the SUBSTRING function. The SUBSTRING function is used to return characters from within another string. The general form of the SUBSTRING function is SUBSTRING(value, position, length)…
-
SQL STUFF Function (Transact SQL)
Description The SQL STUFF function deletes a specified number of characters from a character expression and replaces them with another substring. In other words, it inserts a string into another string. The new string value, position to insert the new string, and the length of the new string are passed as arguments in the function.…
-
SQL RIGHT Function (Transact SQL)
Description The SQL RIGHT function returns the specified number of rightmost characters of a given character expression. The rightmost characters are those at the “end” of the string. SQL RIGHT Usage Notes The character expression as the first argument of the RIGHT function can be a constant string, a variable string, or a database column.…
-
SQL LEFT Function (Transact SQL)
Description The SQL LEFT() function returns the specified number of characters starting from the left side of a given character expression. LEFT The LEFT function is used to return either the beginning or ending portion of a string. LEFT will return the beginning characters; whereas, RIGHT is used to return the ending characters. The general…
-
SQL LEN (Transact SQL)
The SQL LEN function returns the number of characters within a string. Keep in mind the count of characters returned does not include training spaces. Description The SQL LEN function return the length of a string (number of characters). It does not include trailing spaces. The LEN function returns the number of characters in a…
