SQL 2005 String Functions
String Functions are used for manipulating string expression.
(Pass strings using single quotes)
Len('') - Returns length of string.
select Len("Urgent Work") will return 11
Lower('') - Convert all characters to lowercase characters.
select Lower('Urgent Work') will return urgent work
Upper('') - Convert all characters to uppercase characters.
select Upper('Urgent Work') will return URGENT WORK
LTrim('') - Removes spaces from given character strings on left.
select LTrim(' Urgent Work') will return Urgent Work
LTrim & RTRIM & Trim donot remove tab or line feed character.
RTrim('') - Removes space from given character strings on right.
select LTrim('Urgent Work ') returns Urgent Work
Trim('') - Removes spaces from given character strings from both left and right.
select LTrim(' Urgent Work ') returns Urgent Work
SubString('') - Returns a part of string from original string.
SubString(character_expression, position, length)
position - specifies where the substring begins.
length - specifies the length of the substring as number of characters.
Example: select SubString('Urgent Work',1,6) return "Urgent"
Replace('') - Replace the desired string within the original string.
Replace(character_expression, searchstring, replacementstring)
SearchString - string which you want to replace.
ReplaceString - new string which you want to replace with
select replace('Think Big To Achieve Big', 'Big', 'Small')
Original - Think Big To Achieve Big
Result - Think SmallTo Achieve Small
Right('') - extract particular characters from right part of given string expression.
select right('Think High To Achieve High',15) will return "To Achieve High"
This function will be helpful when you want particular characters from right part.
Example: Let say i have social security nos. and i want to extract last 4 digit of it.
select right('111-11-1111',4) will return 1111
select right('222-22-2222',4) will return 2222
select right('333-33-3333',4) will return 3333
select right('444-44-4444',4) will return 4444
