Solved

sorting a varchar datatype

Posted on 2008-10-24
17
813 Views
Last Modified: 2010-04-21
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
Comment
Question by:Kerau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 3

Expert Comment

by:raminhos
ID: 22799502
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 50 total points
ID: 22799522
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
 
LVL 4

Expert Comment

by:FourBeers
ID: 22799531
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Kerau
ID: 22799555
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
 

Author Comment

by:Kerau
ID: 22799622
Four Beers i have thousands of row, so can't manually define each and every data type, what do you think
0
 

Author Comment

by:Kerau
ID: 22799633
MatthewsPatrick, it gives me the same output as my select statement would give
0
 

Author Comment

by:Kerau
ID: 22799709
Any suggestions, I don't wanna make my Friday like the one in Wall Street :(
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 22799960
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
 

Author Comment

by:Kerau
ID: 22800028
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
 

Author Comment

by:Kerau
ID: 22800125
FYI, i am working in ASE 15.0 and not SQL Server, I am using ERStudio's rapid SQL 7.5.5
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22800230
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
 
LVL 19

Accepted Solution

by:
grant300 earned 350 total points
ID: 22805096
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
 

Author Closing Comment

by:Kerau
ID: 31509799
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
 

Author Comment

by:Kerau
ID: 22812162
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22812201
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
 

Author Comment

by:Kerau
ID: 22812252
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question