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:
The SQL LOWER function converts the input character data to a lower case. Description The LOWER function takes character expression as an
The SQL UPPER function converts the input character expression to an upper case and returns it. Description The UPPER function reads the
The SQL PATINDEX function searches an input expression for a given pattern. It returns the starting index of the first occurrence of
The SQL RTRIM function trims all the trailing spaces from the right side of the input character expression. Description The RTRIM function
The SQL LTRIM function removes the leading blank spaces from the input character expression. Description The LTRIM function returns a character expression
The SQL CHARINDEX function returns the starting position of a character expression in another character expression. Description The CHARINDEX function helps with
SQL REPLACE function evaluates an input string for a specified substring pattern and replaces these substrings with another specified string value. Use
Description The SQL SUBSTRING function extracts a part of input expression or a ‘substring’ and returns it. The starting position and the
Description The SQL STUFF function deletes a specified number of characters from a character expression and replaces them with another substring. In
Description The SQL RIGHT function returns the specified number of rightmost characters of a given character expression. The rightmost characters are those
Description The SQL LEFT() function returns the specified number of characters starting from the left side of a given character expression. LEFT
The SQL LEN function returns the number of characters within a string. Keep in mind the count of characters returned does not
