Try this one.

Declare @String varchar(1000)

select @String = '40 ORDER'

SELECT

LTRIM(CASE LEFT(RIGHT(' '+@String,N),1)

WHEN '-' THEN '-' ELSE ''...

...because there's no part of the CASE statement to go to when N is any other value than the one specified, so NULL is assigned...That's an inherent behavior of the...

ok...But my question was

SELECT N

from Tally

where N < 11

ORDER BY CASE WHEN N = 4 THEN 1 END,N

is showing 4 in the last row. How ?

what is happening internally...

SELECT N,CASE WHEN N = 10 THEN 1 ELSE 2 END

from Tally

where N < 11

ORDER BY CASE WHEN N = 10 THEN 1 END,N

output:

22

32

42

52

62

72

82

92

102

11

I also have executed the below code...

SELECT N,CASE WHEN N = 1 THEN 1 ELSE 2 END

from Tally

where N < 11

ORDER BY CASE WHEN N = 1 THEN 1 END,N

output:

22

32

42

52

62

72

82

92

102

11

How...

I did the below testing.

SELECT CASE WHEN N IS NULL THEN N ELSE N END

FROM

(

SELECT N

from Tally

where N < 11

UNION ALL

SELECT NULL

) X

ORDER BY CASE WHEN N IS NULL...

Chris and Jeff,

Sorry a little late here.... Actually i wasn't able to open this one for the last 2 days....When i logged in it didn't open the pages....simply i saw...

Jeff,

Sorry ! a little late here..

🙂

Actually this query is scheduled as night job...what it will do ...it will run on every night and...

Thanks Chris and Jeff !

I know i can get some quality answers from here.Yes...My though is correct..I got the clear answers and from here...Hereafter I never forgot this concept...

But one...

Any inputs ?

Chris,

As you suggested, i have followed your steps.

select reverse(stuff(stuff(reverse(convert(varchar(30),convert(decimal(18,4),234543242.7656))),

charindex('.',reverse(convert(varchar(30),convert(decimal(18,4),234543242.7656))))+4,0,','),charindex('.',reverse(convert(varchar(30),convert(decimal(18,4),234543242.7656))))+8,0,','))

But if i have data...

Christopher,

Thanks for your explanation.

if we give like

CASE WHEN N IS NULL THEN 5 ELSE 4 END

it means

NonNull value will be treated as 4 ( Internally )

Null values will be...

I have modified the code as below

SELECT N

FROM

(

SELECT N

from Tally

where N < 11

UNION ALL

SELECT NULL

) X

ORDER BY CASE WHEN N IS NULL THEN 3 ELSE 2 END,N

It also gave...

So you mean

1- non null values

0- null values

i.e

1 = Non Null = 1

2 = Non Null = 1

3 = Non Null = 1

4 = Non Null = 1

5 = Non...

Thanks for your inputs !

But still i am curious to know the logic used by the below code.

ORDER BY CASE WHEN N IS NULL THEN 1 ELSE 0 END,N

ORDER...