SQL Server provides a range of string manipulation functions. I was aware of most of those in back of the mind, but when I needed to use one, I had to dig it out either from SQL server help file or from google. So, I thought I will list some of the functions which performs some common operations in SQL server. Hope it will be helpful to you all.
Len (' String_Expression' ) - returns the length of input String_Expression.
Example - Select Len('Vipin')
Output - 5
Left ( 'String_Expression', int_characters ) - returns int_characters characters from the left of the String_Expression.
Right ( 'String_Expression', int_characters ) - returns int_characters characters from the right of the String_Expression.
Example - Select Left('Vipin',3), Right('Vipin',3)
Output - Vip, Pin
LTrim ( 'String_Expression' ) - removes spaces from left of the input 'String_Expression'
RTrim ( 'String_Expression' ) - removes spaces from right of the input 'String_Expression'
Note - To removes spaces from both ends of the string_expression use Ltrim and RTrim in conjunction
Example - Select LTrim(' Vipin '), RTrim(' Vipin ') , LTrim ( RTrim(' Vipin '))
Output - 'Vipin ' , ' Vipin' , 'Vipin'
(Single quote marks ' ' are not part of the SQL output, it's just been included to demonstrate the presence of space at the end of string.)
Substring ( 'String_Expression' , int_start , int_length ) - this function returns the part of string_expression. The expression returned is int_length long starting from int_start position.
Example - Select Substring ( 'abcdef' , 2 , 3)
Output - bcd
- if 'String_Expression' is text - value returned is varchar
- if 'String_Expression' is image - value returned is varbinary
- if 'String_Expression' is ntext - value returned is nvarchar
Lower ( 'String_Expression' ) - converts string_expression into lower case.
Example - Select LOWER ( 'Vipin')
Output - vipin
Upper ( 'String_Expression' ) - converts string_expression into upper case.
Example - Select UPPER ( 'Vipin')
Output - VIPIN
Reverse ( 'String_Expression' ) - Reverses the string expression from right to left
Example - Select REVERSE ( 'Vipin')
Output - nipiV
Replace ( 'String_Expression', 'FIND_String', 'REPLACE_String' ) - This function is pretty much similar to 'Find-Replace' dialog box found in editors. This would replace all occurrences of 'FIND_String' with 'REPLACE_String' in 'String_Expression'
Example - Select REPLACE ('Vipin', 'i', 'X')
Output - VXpXn
Also note, string to find is Case-Insensitive.
REPLICATE ( 'String_Expression' , 'Integer_Expression' ) - This function is used to repeat 'String_Expression' for a specified 'Integer_Expression'.
Example - Select REPLICATE ( 'Vipin',2)
Output - VipinVipin
These were just some of the many string manipulation functions available in SQL Server 2000/2005. More comprehensive details are available on comprehensive SQL server help file. However, you can also refer to examples of the functions given here.