Solved

sorting a varchar datatype

Posted on 2008-10-24
17
773 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
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
 

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 142

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 142

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 142

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now