String manipulation functions in SQL Server 2000 / 2005

Posted by Vipin on Geeks with Blogs See other posts from Geeks with Blogs or by Vipin
Published on Fri, 26 Mar 2010 14:07:03 GMT Indexed on 2010/03/26 15:23 UTC
Read the original article Hit count: 223

Filed under:

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.

© Geeks with Blogs or respective owner