Solved

sorting a varchar datatype

Posted on 2008-10-24
17
820 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
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 

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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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