Use of CHARINDEX Function in SQL Server ?

CHARINDEX function is used to find the position of the expression in a given character expression.

Syntax:

CHARINDEX (ExpressionToFind,CharacterExpression,[Starting_Position])

Example:

SELECT CHARINDEX('SQL','JK SQL SERVER BLOG SPOT JK SQL SERVER')

Output:

4

Explanation:

The above example will return the position of the 'SQL' in the character expression.The expression 'SQL' is found in the 4th position of the character expression.

So it returns the output value as 4.

Starting_Position in CHARINDEX function is optional.

Let's see another example which uses the optional parameter.

If you want to return the position of the 'SQL' after the 4th position the you can use the following syntax.

SELECT CHARINDEX('SQL','JK SQL SERVER BLOG SPOT JK SQL SERVER',5)

Now the output value will be 28.

Now the function will skip the value from 1st to 4th character , then it will search for the next occurrence of the word in the character expression.

And finally it found the value at the 28th position in the character expression.So it returns the value as 28.

Note : If you want to find the position of the character from the starting position itself then no need to use the starting_position parameter if not so then you can go head with 3 parameters.

Use of LTRIM and RTRIM function in SQL server ?

LTRIM FUNCTION

LTRIM function is used to remove the leading blanks(i.e towards right side) from the character expression

Syntax:

LTRIM(CHARACTER EXPRESSION)

Example:

SELECT LTRIM('           SQL SERVER')

Output:

SQL SERVER

RTRIM FUNCTION

RTRIM function is used to remove the trailing blanks(i.e towards left side) from the character expression

Syntax:

RTRIM(CHARACTER EXPRESSION)

Example:

SELECT RTRIM('SQL SERVER                ')

Output:

SQL SERVER

Use of REVERSE function in SQL server ?

REVERSE function is used to reverse the order of a sting value

Syntax:

REVERSE(STRING EXPRESSION)

Example:

SELECT REVERSE('JK SQL SERVER BLOG SPOT')

Output:

TOPS GOLB REVRES LQS KJ

Use of REPLICATE function in SQL server ?

REPLICATE function is used to repeat the sting value a specified no of times

Syntax:

REPLICATE(STRING EXPRESSION,INT EXPRESSION)

Example:

SELECT REPLICATE('SQL',5)

Output:

SQLSQLSQLSQLSQL

Use of UPPER and LOWER function in SQL server ?

UPPER FUNCTION

UPPER function is used to get the Upper case character of the given character expression

Syntax:

UPPER(CHARACTER EXPRESSION)

Example:

SELECT UPPER('jk sql server blog spot')

Output:

JK SQL SERVER BLOG SPOT

LOWER FUNCTION

LOWER function is used to get the Lower case character of the given character expression

Syntax:

LOWER(CHARACTER EXPRESSION)

Example:

SELECT LOWER('JK SQL SERVER BLOG SPOT')

Output:

jk sql server blog spot

Use of Left and Right function in SQL server ?

LEFT FUNCTION

Left function is used to get the left most character of the expression based on the given length

Syntax:

LEFT(CHARACTER EXPRESSION,LENGTH)

Example:

SELECT LEFT('SQL SERVER',3)

Output:

SQL

RIGHT FUNCTION

RIGHT function is used to get the right most character of the expression based on the given length

Syntax:

RIGHT(CHARACTER EXPRESSION,LENGTH)

Example:

SELECT RIGHT('SQL SERVER',6)

Output:

SERVER

Use of ASCII and char function in SQL server ?

ASCII FUNCTION

ASCII (American Standard Code for Information Exchange) function is used to get the ASCII value of the given character expression

Syntax:

ASCII(CHARACTER EXPRESSION)

Example:

SELECT ASCII ('A')

Output:

65

CHAR FUNCTION

CHAR function is used to convert an ASCII value to a character

Syntax:

CHAR(INT)

Example:

SELECT CHAR (65)

Output:

A

Difference between Replace Function and Stuff function in SQL server ?

Replace:

• Replace function will replace all exact occurrence of the string in a character expression. 
• Replace function will take 3 parameters as input.

Stuff: 

• Stuff function will replace only the exact occurrence of the string in a character expression based on the start and length values.
• Stuff function will take 4 parameters as input.

The above points are the major difference between Replace and stuff function.

If you want to replace all the string occurrence then prefer replace function ,if not so then go with stuff function.

Use of STUFF function in SQL Server?

Stuff function is used to replace the occurrence of the String in a Expression based on the start value and length value.

Syntax : 

STUFF ([CHARACTER EXPRESSION],[START],[LENGTH],[REPLACEMENT STRING]

Example :

SELECT STUFF('JK SQLBLOG',7,1,'SERVER ') 

In above example replacement string 'SEVER' will be Inserted in the character expression.

It search for the character position 7 in the character expression and after that it will insert the replacement string.

Output :

JK SQLSERVER LOG

Use of Replace function 

http://jksqlserver.blogspot.ae/2016/03/use-of-replace-function-in-sql-server.html

Replace Vs Stuff

http://jksqlserver.blogspot.ae/2016/04/difference-between-replace-function-and.html

Use of SUBSTRING Function in SQL Server

SUBSTRING function is used to select the Part of the String in a Expression 

Syntax : 

SUBSTRING('STRING EXPRESSION','START','LENGTH')

Example :

SELECT SUBSTRING('JK SQL SEVER BLOG',4,20)

Output :

SQL SERVER BLOG

In above example ,the substring function will select the string from 4th character to 20th character.
Using this function we can select the required part of the string in a expression.

Use Of Replace Function in SQL Server

Replace function is used to replace the exact occurrence of the String in a Expression 

Syntax : 

REPLACE '[STRING EXPRESSION]','[STRING TO FOUND]',[REPLACEMENT STRING]

Example :

SELECT REPLACE('JK SQL SEVER BLOG','SEVER','SERVER') 

In above example 'SEVER' will be replaced with replacement string 'SERVER' in the string expression.

Note : Replace function will replace all the exact occurrence of the string expression

rather than replacing particular string occurrence.