SQL Server built-in String Functions with example
In this article, I will explain some of the SQL Server built-in String Functions with example.
some of the built in function like
lenth of the string LEN,LTRIM-left trim,RTRIM rigth trim,LEFT,RIGTH
SUBSTRING,REPLACE, string REVERSE
string LOWER and string UPPER
SQL Server built-in functions
A function performs an operation and returns a value
A function consists of the function name, followed by a set of parenthesis that contains any parameter or arguments required by the function.
String Functions
1.LEN (string) - Returns the length of the characters in the specified string expression, excluding trailing blanks
Example:
Select LEN('Raj'), LEN('Raj ') FROM VENDOR WHERE VendorFName='Raj'
2.LTRIM (string) - LTRIM function to return a character expression after removing leading spaces.
Example
SELECT LTRIM(' Raj')
FROM VENDOR WHERE VendorFName='Raj'
3.RTRIM (string) - RTRIM function to return a character expression after removing trailing spaces.
Example
Select RTRIM('Raj ')
FROM VENDOR WHERE VendorFName='Raj'
4.LEFT (string, length) - Returns the specified number of characters from the beginning of the string.
Example
SELECT VendorFName, VendorLName, LEFT(VendorFName, 1) + LEFT (VendorLName, 1) AS Initials FROM Vendor
5.RIGTH (string, length) - Returns the specified number of characters from the end of the string.
Example
SELECT VendorFName, VendorLName, RIGHT(VendorFName, 1) + RIGHT (VendorLName, 1) AS Initials FROM Vendor
6.SUBSTRING (string, start, length) - Returns the specified number of characters from the string starting at the specified position.
Example
SELECT SUBSTRING('beniwal', 2, 2) FROM VENDOR WHERE VendorFName='Raj'
7.REPLACE (search, find, replace) - Returns the search string with all occurrences of the find string replaced with the replace string.
Example
SELECT REPLACE('Beniwal', 'Beniwal', 'Choudhary')
FROM VENDOR WHERE VendorFName='Raj'
8.REVERSE (string) - Returns the string with the character in reverse order.
Example
SELECT REVERSE('Raj')
FROM VENDOR WHERE VendorFName='Raj'
9.CHARINDEX (find, search [, start]) - Returns an integer that represents the position of the first occurrence of the find string in the search string starting at the specified position. If the starting position isn't specified, the search starts at the beginning of the string. If the staring isn't found, the functions returns zero.
Example
SELECT CHARINDEX('w', 'Beniwal')
FROM VENDOR WHERE VendorFName='Raj'
10.PATINDEX (find, search [, start]) - PATINDEX is useful with text data types; it can be used in a WHERE clause in addition to IS NULL, IS NOT NULL, and LIKE (the only other comparisons that are valid on text in a WHERE clause). If either pattern or expression is NULL, PATINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, PATINDEX returns NULL only when both pattern and expression are NULL.
Example
SELECT PATINDEX('%Krew%', VendorLName)
FROM VENDOR WHERE VendorId=5
11. LOWER (string) - Returns the string converted to lowercase letters.
Example
SELECT LOWER('Rajesh')
FROM VENDOR WHERE VendorFName='Rajesh'
12. UPPER (string) - Returns the string converted to uppercase letters.
Example:
SELECT UPPER('Raja')
FROM VENDOR WHERE VendorFName='Raja'
13.SPACE (integer) - Returns the string with the specified number of space characters (blanks).
Example
SELECT VendorFName + ',' + SPACE(2) + VendorLName
FROM VENDOR WHERE VendorFName='Raj'