Posts
33
Comments
135
Trackbacks
0
March 2010 Entries
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 | Comments (2)
Find the occurrence of word/character in SQL column with wildcard character - PATINDEX

CharIndex and PatIndex both can be used to determine the presence of character or string within sql column data. Both returns the starting position of the first occurrence of the character/word within expression.

However, one major difference between CharIndex and PatIndex is that later allows the use of wild card characters while searching for character or word within column data.

Also, Patindex is useful for searching within Text datatype.

Allowed wild card characters are % and _ .

" % "  - use it for any number of characters

" _ "  - use it for a single character.

Syntax

PATINDEX('%pattern%', string_expression)

Note - it's mandatory to include pattern within %% characters.

  • returns starting position of occurrence of pattern, if found.
  • returns 0, if not found
  • returns NULL , if either pattern or string_expression is null.

Example

  1. SELECT fldname FROM tblUsers WHERE PatIndex('%v_pin%', fldname) > 0

 

Posted On Thursday, March 25, 2010 5:35 PM | Comments (5)
How to find the occurrence of particular character in string - CHARINDEX

 

 

Many times while writing SQL, we need to find if particular character is present in the column data. SQL server possesses an in-built function to do this job -

Syntax 

CHARINDEX(character_to_search, string [, starting_position])

Returns the position of the first occurrence of the character in the string.

NOTE - index starts with 1. So, if character is at the starting position, this function would return 1.

Returns 0 if character is not found.

Returns 0 if 'string' is empty.

Returns NULL if string is NULL.

Example

SELECT CHARINDEX('a', fname) a_First_occurence,

CHARINDEX('a', fname, CHARINDEX('a', fname)) a_Second_occurrence

FROM Users

WHERE fname = 'aka unknown'

OUTPUT

a_First_occurence a_Second_occurrence
1 3

 

 

Posted On Tuesday, March 23, 2010 3:48 PM | Comments (4)