Various string manipulation functions in SQL Server 2000 / 2005

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

  1. if  'String_Expression'  is text - value returned is varchar
  2. if  'String_Expression'  is image - value returned is varbinary
  3. 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.

posted on Friday, March 26, 2010 3:07 PM Print
# re: Various string manipulation functions in SQL Server 2000 / 2005
Jospeh Amal raj
6/4/2012 12:07 AM
basically it is super
# re: Various string manipulation functions in SQL Server 2000 / 2005
8/28/2012 7:32 PM
i am so happy to say to you because of learnt some basic string manipulation functions

Post Comment

Title *
Name *
Comment *