SQL Server Built-in functions

Difference between stuff and replace

Stuff () function will accepts 4 parameters. Stuff will replace the characters from specific length to specific length with specific characters.

Syntax: stuff (expression1, starting_postion, number of chars, expression)

--1 original value,

--2 starting position

--3 number of chars

--4 replace value

declare @value varchar(20)='a.b.c.d.e'

select stuff(@value,2,1,'')  AS stuff

Here it will replace the second character will (.’) replace with (‘’).

The output will look like this.

stuff

ab.c.d.e

Replace () function will accepts 3 parameters. Replace will replace specific character with specific character in all places

Syntax: replace (expression,expression,expression)

--1 original value,

--2 old char to be replaced in the original value)

--3 new char to be replaced the old value

Example:

Declare @value varchar(20)='a.b.c.d.e'

Select replace (@value,'.','') as replace

replace

abcde

Len returns the length of the specified value.

Syntax: Len (expression)

DATALENGTH function returns the actual length of the value(for fixed length data type).

Syntax: Datalength(expression)

Then below example shows the difference between len and datalength function.

declare @test char(2);

set @test='A'

select DATALENGTH(@test) as datalength, len(@test) as len

datalength    len

2             1

coalesce:coalesce function returns the first not null value.

Syntax: coalesce(expression, expression1, expressio2..n)

isnull:isnull function replace the null value with the given value.

Isnull(expression,expression1).

isnull is sqlserver function. Coalesce is oracle function.but sqlserver supports the coalesce function.

isnull is requires only two aruments where as Coalesce function accepts more than two arguments.

Example:

declare @test varchar(10);

select coalesce(@test,null,null,1) as 'coalesce',isnull(@test,1) as 'isnull'

Result

1         coalesce  isnull

2         1         1

Cast:  CAST function is used to convert the given data from one data type to other data type.

Syntax: CAST(variable AS datatype)

It will cast the variable into the given format.

Example:

select cast('aaa' as varchar)

the ouput is ‘aaa’.

By default the length of varchar data type when using cast is 30.if we did not declare the length of varchar and if given variable is more than 30 characters it will eliminate the other characters.

Convert function is used to convert the given data from one data type to other data type.

Syntax: Convert (datatype,input,dataformat(optional))

It will cast the variable into the given format.

Example:

select convert(varchar,'aaa')

the ouput is ‘aaa’.

By default the length of varchar data type when using convert is 30.if we did not declare the length of varchar and if given variable is more than 30 characters it will eliminate the other characters.

By default if we did not declare the length of varchar or char the default length is 1.

DECLARE @TEST VARCHAR

SET @TEST='AAAA'

SELECT @TEST

The output of the query is 'A'

for cast and convert the default length of varchar or char is 30.

select cast('aaaa34543333333454363465sds3563565geryehd' as char) as cast,convert( char,'aaaa34543333333454363465sds3563565geryehd') as convert

It will give output as

cast                            convert

aaaa34543333333454363465sds356 aaaa34543333333454363465sds356

if we declare full length of the data type then it will return the output as expected.

select cast('aaaa34543333333454363465sds3563565geryehd' as char(100)),convert( char(100),'aaaa34543333333454363465sds3563565geryehd')

aaaa34543333333454363465sds3563565geryehd ---cast                                                         aaaa34543333333454363465sds3563565geryehd  --convert

declare @date datetime =getdate()

select cast(@date as varchar) cast,convert(varchar,@date,101) as '101',convert(varchar,@date,102) as '102'                                                         

the output of the given query is


cast 101 102
Nov 20 2012  6:52PM 11/20/2012 2012.11.20

cast function accepts only two parameters where as convert function accepts the  third parameter which is used for setting different date time formats.