Commonly used String Functions

            Several functions are available in SQL server, in those commonly used functions are

• String functions
• SUBSTRING()
• LTRIM()
• RTRIM()
• LEFT()
• RIGHT()
• REPLACE()
• LOWER()
• UPPER()
• LEN()
• ASCII()
• CHAR()
• REVERSE()
• CHARINDEX()
• PATINDEX()
• STUFF()

SUBSTRING(): Using SUBSTRING function we can extract part of the string from given string.

            Syntax: SUBSTRING (Expression, start, length)

            Example: SELECT SUBSTRING('SQL server',1,3);

LTRIM(): It removes leading blank spaces of a string.

            Syntax: LTRIM (String)

            Example: SELECT LTRIM('   SQL SERVER')

RTRIM(): It removes trailing blank spaces of a string.

Syntax: RTRIM (String)

            Example: SELECT RTRIM('SQL SERVER   ')

LEFT(): Returns the left most characters of a string.

Syntax: LEFT (string, length)

Example: SELECT LEFT('SQL SERVER',3)

RIGHT(): Returns the right most characters of a string.

Syntax: RIGHT (string, length)

Example: SELECT RIGHT('SQL SERVER',6)

REPLACE(): Returns a string with all the instances of a substring replaced by another substring.

Syntax: REPLACE (find, replace, string)

Example:

SELECT REPLACE('This is SQL','SQL','structured query language')

LOWER(): Using this function we can change the string case to lower.

            Syntax: LOWER (String)

            Example: SELECT LOWER('SQL Server')

UPPER(): Using this function we can change the string case to upper.

            Syntax: UPPER (String)

            Example: SELECT UPPER('SQL Server')

LEN(): This function returns length of the string.

            Syntax: LEN (String)

            Example: SELECT LEN('SQL Server')

ASCII(): It returns the ASCII code value of the leftmost character of a character expression.

            Syntax: ASCII (String)

            Example: SELECT ASCII('SQL Server')

CHAR(): It converts an int ASCII code to a character.  

            Syntax: CHAR (Integer expression)

           

            Example: select CHAR(83)

REVERSE(): It returns a character expression in reverse order. 

            Syntax: REVERSE (String)

            Example: SELECT REVERSE('SQL Server')

CHARINDEX (): Char Index returns the first occurrence of a string or characters within another string.

Example: I want to find the first occurrence of the letter ‘r’ from the string “SQL Server”.

           

            SELECT CHARINDEX('R','SQL Server')

PATINDEX(): As a contrast PatIndex is used to search a pattern within an expression.

            Syntax: PATINDEX ( '%pattern%' , expression)

Here the first argument takes a pattern with wildcard characters like '%' (meaning any string) or '_' (meaning any character).

Example: SELECT PATINDEX('%BC%','ABCD')

STUFF(): Using this function we can replace specific length of characters with another set of characters.

Syntax: STUFF (character_expression1, start, length, character_expression2)

            Example: SELECT STUFF('SQL Server is useful',5,6,'Database')