• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 850
  • Last Modified:

sorting a varchar datatype

i have a table called dbo.AbcTable where i have sixteen different columns with different data types. One of the column is RuleNumber varchar(40) null. This has different rule numbers i want to sort this according to the ascending or decending order.

lets say i do this
SELECT * FROM dbo.abcTable order by RuleNumber. I get thousands of rows (with integers, characters, symbols) but not in a fashion i desire.

this is what i get(sample)
100
1034
1035.2
1221
200
205.2
206.1
20951
test
test123

If you are thinking that the command should yeild the same value as shown above, then yes, you are right! That is the result I will get when i use order by command for a varchar datatype. But my issue is how do i sort this as shown below, please suggest

100
200
205.2
206.1
1034
1035.2
1221
20951
test
test123
 
0
Kerau
Asked:
Kerau
3 Solutions
 
raminhosCommented:
Try both:


SELECT cast(RuleNumber as int) FROM dbo.abcTable order by RuleNumber
 
or
 
SELECT cast(RuleNumber as int) FROM dbo.abcTable order by cast(RuleNumber as int)

Open in new window

0
 
Patrick MatthewsCommented:
ORDER BY ISNUMERIC(RuleNumber) DESC, CASE WHEN ISNUMERIC(RuleNumber) = 1 THEN RIGHT('000000000' + CONVERT(varchar(20), CONVERT(numeric(10, 2), RuleNumber)), 18) ELSE RuleNumber END
0
 
FourBeersCommented:
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
KerauAuthor Commented:
Thanks raminhos for the quick reply. I did try this approach, again i tried with no success :(

SELECT cast(RuleNumber as int) FROM dbo.abcTable order by RuleNumber
Syntax error during explicit conversion of varchar value 1035.2 to a INT field
 
or
 
SELECT cast(RuleNumber as int) FROM dbo.abcTable order by cast(RuleNumber as int)
Syntax error during explicit conversion of varchar value 208.1 to a INT field
0
 
KerauAuthor Commented:
Four Beers i have thousands of row, so can't manually define each and every data type, what do you think
0
 
KerauAuthor Commented:
MatthewsPatrick, it gives me the same output as my select statement would give
0
 
KerauAuthor Commented:
Any suggestions, I don't wanna make my Friday like the one in Wall Street :(
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try the suggestion of matthewspatrick, that looks like it should work.
maybe you have a "," as decimal separator for your sql server, hence the "." will make the isnumeric returns 0 for the "numerical" data?

you might try this approach:
select ...
  from ...
 order by case when RuleNumber like '%^[0-9.]%' then 1 else 0 end
     , case when RuleNumber like '%^[0-9]%' then right('0000000000' + RuleNumber , 10) + '.00000' 
            when RuleNumber like '%^[0-9.]%' then right('0000000000' + substring(RuleNumber, charindex('.', RuleNumber)-1) , 10) + substring(RuleNumber, charindex('.', RuleNumber), 5)  end
     , RuleNumber

Open in new window

0
 
KerauAuthor Commented:
Thanks AngelIII i am trying with different options. The approach you took yielded this error

Function SUBSTRING invoked with wrong number or type of argument(s)
0
 
KerauAuthor Commented:
FYI, i am working in ASE 15.0 and not SQL Server, I am using ERStudio's rapid SQL 7.5.5
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, I don't know ASE actually, soo ... I can only guess.
select ...
  from ...
 order by case when RuleNumber like '%^[0-9.]%' then 1 else 0 end
     , case when RuleNumber like '%^[0-9]%' then right('0000000000' + RuleNumber , 10) + '.00000' 
            when RuleNumber like '%^[0-9.]%' then right('0000000000' + substring(RuleNumber, 1,charindex('.', RuleNumber)-1) , 10) + substring(RuleNumber, charindex('.', RuleNumber), 5)  end
     , RuleNumber

Open in new window

0
 
grant300Commented:
Actually, angelIII, you are really close.  After you use the REGEX to determine if the Rule "Number" consists of numerics with or without a decimal, you can convert it from a string to a Real or Float otherwise return a null.  Likewise, the rows/values that don't match the REGEX stay as a string or you return a null.

The trick is to do it in two columns, not one, since there is no way to resolve the differing data types within a single column.

If the numeric and textual rule number blocks come back in the wrong order, you can simply reverse the order of the CASE statements.

Regards,
Bill

order by case when RuleNumber like '%[0-9.]%' then convert(float, RuleNumber) else null end,
         case when Rulenumber like '%^[0-9.]%' then RuleNumber else null end

Open in new window

0
 
KerauAuthor Commented:
Ureka!!!! This works :). You all are geniuses, this is why EE is the number 1 forum to seek help. Keep up the good work and thanks a ton!!
0
 
KerauAuthor Commented:
FYI, this doesn't bother me much but just for all to know the order by statement doesn't aligns textual values properly. Number comes in the exact same order as they should be.Eg. I am getting something like this

Tom
safsdfdf
Cat Tom
Cat Tom
100
1000
1000.2
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to add a bit more:
order by case when RuleNumber like '%[0-9.]%' then convert(float, RuleNumber) else null end,
         case when Rulenumber like '%^[0-9.]%' then RuleNumber else null end, RuleNumber

Open in new window

0
 
KerauAuthor Commented:
Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now