String manipulation functions in SQL Server 2000 / 2005
- by Vipin
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.
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.
Right ( 'String_Expression', int_characters ) - returns int_characters characters from the right of the String_Expression.
