SQL SERVER - String Functions

String Functions have wide usage in SQL Server. We will see the frequently used String Functions in SQL Server 2005.

1. SUBSTRING(): Returns part of the string or a column

   Syntax : SUBSTRING(expression, Start, Length)

   Expression : Can be either character string or binary string or text or image or a column.

   Start : an integer from where the substring starts

   Length : an integer that represents the length of the Substring

   Example:  Select SUBSTRING('SQL Server 2008',12,4) as Version

    Result :

2. LEFT() &  RIGHT(): Returns certain number of characters either from Left Or Right side of the character expression.

Syntax :        LEFT (Character expression, integer)      

                  RIGHT(Character expression, integer)

   Character expression : Can be either Char  or varchar or nvarchar or Binary data types

   Integer  : The number of characters that will be returned

   Example:  Select Left('SQL Server 2008',3) as RDBMS 
                   Select Right('SQL Server 2008',4) as Version

    Result :

3. CHARINDEX() & PATINDEX(): Returns the starting position or First Occurence of the specified character expression in the given string. In the case of CHARINDEX() we can not use the Text data type where as in PATINDEX() we can use.Also we can use Wild card characters in PATINDEX() where as in CHARINDEX() we cannot.

Syntax : CHARINDEX(Character Expr1, Character  Expr2,[Start])                                                  

             PATINDEX('%Expression%',Character Expr2)

   Character Expr1 : Character(s) that needs to be find out.

   Character Expr2 : String or a column where we need to search the Character Expr1.

   Start :  An integer from where the search starts. This Parameter is Optional.

Expression :  Character(s) that needs to be find out.  Here Wild card characters can be used

Example:                                                                Select PatIndex('%is%','I wish towish') [First Occurrence]
Select CharIndex('to','I wish to wish') [First Occurrence]
Select CharIndex('is','I wish to wis',10) [Second Occurrence]

    Result :   

 

4. LTRIM() & RTRIM(): Trims all the leading and trailing  blanks in a character string .

Syntax :        LTRIM(Character expression)      

                  RTRIM(Character expression)

    Character Expression : Can be a character string or column or Binary data

   Example:    Select LTRIM ( '    XYZ') 
                    Select RTRIM( 'XYZ   ')

    Result :

   

5. LOWER() & UPPER(): Converts the character string’s Case to either Lower or Upper.

Syntax :        LOWER(Character expression)      

                  UPPER(Character expression)

    Character Expression : Can be a character string or column or Binary data

   Example:    Select LOWER ( 'ABC') LowerCase 
                      Select UPPER( 'abc') UpperCase

    Result :

    

6. REPLACE() : Replaces all occurrences of a specified string with another string

    Syntax : REPLACE(expression1,expression2,expression3)

   Expression1 : String to be searched.

   Expression2 : String to be Replaced.

   Expression3 : String with which Expression2 has to be replaced.

   Example:  Select REPLACE('SQL Server 2005','2005','2008') NEW_VERSION

    Result :

7. STUFF() :  Deletes a specified length of characters and inserts another set of characters at the starting point specified.

    Syntax:   STUFF(Expression1,start,length,Expression2)

   Expression1 : It can be either Character string or Column or Binary data in which Deletions\Insertions will be made.

   Start : Starting Position from where the Deletion/Insertion takes place

   Length : Number of characters to be Deleted

   Expression2 : Character String to be Replaced.

   Example:  Select STUFF('SQL Server 2005',12,4,'2008') NEW_VERSION

    Result :

8. LEN() : Returns the number of characters of the specified string

    Syntax:   LEN(Expression)

   Expression : It can be either Character string or Column, of which the length has to be calculated

   Example:  Select LEN('SQL Server 2008') Length

    Result :

9. REVERSE() : Returns the Reverse of the string expression.

    Syntax:   REVERSE(Expression)

   Expression : It can be either Character string or Column or Binary Data.

   Example:  Select REVERSE('1234567890') 
                       GO
                       Select REVERSE('REVERSE') 

    Result :

10. REPLICATE() :  Repeats the character expression a specified number of times

    Syntax:   REPLICATE(Expression,Integer)

   Expression : It can be either Character string or Column or Binary Data.      

   Example:  Select REPLICATE('$',10)  Repetition

    Result :