?
Solved

sorting a varchar datatype

Posted on 2008-10-24
17
Medium Priority
?
827 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 

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 400 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 1400 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

800 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