CODEWITHSUNDEEP
sqlsql-servercasting
bojackh
bojackh

Reputation: 97

CAST value with two decimals - 0.00.0

I'd like to order values with two decimals/periods and wondering if there's CAST/Convert datatype that will allow me to do this. Values are currently something like this:

1.11.1
1.11.10
1.11.11
1.11.2
1.11.21
1.11.3

I'd like them to be ordered like the following:

1.11.1
1.11.2
1.11.3
1.11.10
1.11.11
1.11.21

Upvotes: 3

Views: 313

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82010

Perhaps something like this

Declare @YourTable table (SomeField varchar(50))
Insert into @YourTable values
('1.11.1'),
('1.11.10'),
('1.11.11'),
('1.11.2'),
('1.11.21'),
('1.11.3')

Select A.* 
 From @YourTable A
 Order By 
       cast(ParseName(SomeField,4) as int)
      ,cast(ParseName(SomeField,3) as int)
      ,cast(ParseName(SomeField,2) as int)
      ,cast(ParseName(SomeField,1) as int)

Returns

SomeField
1.11.1
1.11.2
1.11.3
1.11.10
1.11.11
1.11.21

Upvotes: 4

S3S
S3S

Reputation: 25152

You can do this by splitting your values in a CTE and then ordering... Just replace #t with your table and c1 with your column name.

;with cte as(
select 
    c1, 
    LEFT(c1,CHARINDEX('.',c1) - 1) as LeftDigits,
    SUBSTRING(c1,CHARINDEX('.',c1) + 1,LEN(c1) - CHARINDEX('.',c1) - CHARINDEX('.',reverse(c1))) as MiddleDigits,
    RIGHT(c1,CHARINDEX('.',reverse(c1)) - 1) as RightDigits
from #t)


select * from cte
order by
LeftDigits,MiddleDigits,LEN(RightDigits),RightDigits

Here is some test data for everyone

select '1.11.1' as C1 into #t
union all select
'1.11.10'
union all select
'1.11.11'
union all select
'1.11.2'
union all select
'1.11.21'
union all select
'1.11.3'
union all select
'2.11.1'
union all select
'2.1.1'
union all select
'2.2.1'
union all select
'33.0.5'
union all select
'2.01.11'

And results....

Results

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can use this query but it is round about way

select numbers
from #testdecimal
order by convert(int,substring(substring(numbers,charindex('.', numbers)+1, len(numbers)), charindex('.', substring(numbers,charindex('.', numbers)+1, len(numbers)))+1, len(numbers)))

create table script:

create table #testdecimal
( numbers nvarchar(20)
)

insert into #testdecimal (numbers)
values
 ('1.11.1'  ),('1.11.10' ),('1.11.11' )
,('1.11.2'  ),('1.11.21' ),('1.11.3'  )

Upvotes: 0

Related Questions