Almost All Strings function of sql 2008

Almost all Strings Manipulation function of sql 2008 like Substring, Left, Right , Stuff, Replace, Replicate...........

This post is to show all string functions of SQLwith examples  and How to use them in TSQL.

We will take a string 'Bangalore' and Apply all function in that string.

--Stuff Replacing value from 'ang' to 'abc' position 2-4 to 'abc' output babclore

select 'bangalore',STUFF('bangalore',2,4,'abc')

--Return value from Position 2-4  output ang

select 'bangalore',substring('bangalore',2,4)

--Replace values b to z output Zangalore

select 'bangalore',REPLACE('bangalore','B','Z')

--Replicate 'bang' word 5 time Output 'bangbangbangbangbang'

select 'bangalore', Replicate('bang',5)

--Reverse the value of bangalore output erolagnab

select 'bangalore', reverse('bangalore')

--Take three values from left Output 'Ban'

select 'bangalore',LEFT('bangalore',3)

--Take three values from right Output 'ore'

select 'bangalore',right('bangalore',3)

--convert values into upper case Output 'BANGALORE'

select 'bangalore',UPPER('bangalore')

--convert values into lower case Output 'bangalore'

select 'bangalore',lower('bangalore')

--Search the pattern and return starting position of  pattern  value if pattern not found returns zero

--output 2

select 'bangalore',PATINDEX('%ang%','bangalore')

--Search the value and return  position of  value if value not found returns zero

--output 2

select 'bangalore',charindex('n','bangalore')

--Count no of Character and  return no. of characters  it include space also Output -13

select 'bangalore' ,LEN(' bangalore is')

--returns no. of byte in a string

select 'bangalore' ,DATALENGTH('bangalore is')

--Trim white space from right side of string

select 'bangalore',RTRIM(' bangalore ')

--Trim left space from right side of string

select ' bangalore',lTRIM(' bangalore')

Note: In above function we have taken the example of a string 'bangalore' in order to use it with tables replace 'bangalore' with your column names'