1. Registered User
Join Date
Sep 2003
Location
Posts
7

In my table there is a field with type varchar and this field contains both numeric and alphanumeric values. I need to sort the data such that numeric values should be sorted based on numeric value (1,2,3,4,5,6,7,8,9,10,11...) and alphanemeric values should be order by aplhabeticas (a,b,c,d...). Numeric values should appear first.

My data is like this
1.10, 1.20, 10.10, 10.20, 2.10, 2.20, R1.10, R1.20, R10.10, R10.20, R2.10, R2.20,

I want like this
1.10, 1.20, 2.10, 2.20, 10.10, 10.20, R1.10, R1.20, R2.10, R2.20, R10.10, R10.20

2. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447

Re: Sorting Problem

Your problem is that your table isn't normalized as it contains three values in one field:

a) numeric / alphanumeric indication
b) major number
c) minor number

You want a sorting like
ORDER BY a ASC, b ASC, c ASC

which requires the need of a specification of your three parts out of your one field:

a: case when left(<YourField>,1) between '0' and '9' then '0' else left(<YourField>,1) end

c: case when charindex( '.',<YourField>)=0 then '' else substring(<YourField>,charindex( '.',<YourField>)+1, len(<YourField>)) end

b: substring(<YourField>, len(a)+1, len(<YourField>)-len(a)-len(c)-case when len(c)=0 then 0 else 1 end)
whereby the length of a is zero if there is not alphanumeric indication.

SELECT * FROM <YourTable> ORDER BY
case when left(<YourField>,1) between '0' and '9' then '0' else left(<YourField>,1) end ASC,
substring(<YourField>, len(case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end)+1, len(<YourField>)-len(case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end)-len(case when charindex( '.',<YourField>)=0 then '' else substring(<YourField>,charindex( '.',<YourField>)+1, len(<YourField>)) end)-case when len(case when charindex( '.',<YourField>)=0 then '' else substring(<YourField>,charindex( '.',<YourField>)+1, len(<YourField>)) end)=0 then 0 else 1 end) ASC,
case when charindex( '.',<YourField>)=0 then '' else substring(<YourField>,charindex( '.',<YourField>)+1, len(<YourField>)) end ASC

Cheers!
Last edited by DoktorBlue; 09-01-03 at 04:51.

3. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447

Re: Sorting Problem

I forgot a detail:

b) and c) have to be converted to a number. So, in the order by clause, replace a ASC, b ASC, c ASC by
ORDER BY a ASC, cast(b as int) ASC, cast(c as int) ASC

which results in this query:

SELECT * FROM <YourTable> ORDER BY
case when left(<YourField>,1) between '0' and '9' then '0' else left(<YourField>,1) end ASC,
cast(substring(<YourField>, len(case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end)+1, len(<YourField> )-len(case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end)-len(case when charindex( '.',<YourField> )=0 then '' else substring(<YourField>,charindex( '.',<YourField> )+1, len(<YourField> )) end)-case when len(case when charindex( '.',<YourField> )=0 then '' else substring(<YourField>,charindex( '.',<YourField> )+1, len(<YourField> )) end)=0 then 0 else 1 end) as INT) ASC,
cast(case when charindex( '.',<YourField> )=0 then '0' else substring(<YourField>,charindex( '.',<YourField> )+1, len(<YourField> )) end as INT) ASC

4. Registered User
Join Date
Sep 2003
Location
Posts
7
It is working fine for the example data given by me in my first query.
But my data have R.R/15.10, UG/18.20 for which it is unable to convert to INT. No other posiblilites of data other than I mentioned in my column.

5. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
It is being the same problem, but you will have to change the definition of your first portion a), and you will have to take into account that you may have two points during determination of c).

a: case when charindex('/',<YourField>)=0 then case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end else substring(<YourField>,1,charindex('/',<YourField>))

c (replace a) where occuring: case when charindex( '.',substring(<YourField>, len(a)+1, len(<YourField>)) )=0 then '' else substring(<YourField>,charindex( '.',substring(<YourField>, len(a)+1, len(<YourField>) )+1, len(<YourField> )) end

b (keeps the same, replace a & c where occuring): substring(<YourField>, len(a)+1, len(<YourField> )-len(a)-len(c)-case when len(c)=0 then 0 else 1 end)

Use a) as described within b) and c), but change a) when writing your ORDER BY at two positions:
case when charindex('/',<YourField>)=0 then case when left(<YourField>,1) between '0' and '9' then '0' else left(<YourField>,1) end else substring(<YourField>,1,charindex('/',<YourField>)-1)

Also use c) within b) as desribed above, but change it in this way, when writing your ORDER BY:

case when charindex( '.',substring(<YourField>, len(a)+1, len(<YourField>)) )=0 then '0' else substring(<YourField>,charindex( '.',substring(<YourField>, len(a)+1, len(<YourField>) )+1, len(<YourField> )) end

6. Registered User
Join Date
Sep 2003
Location
Posts
7
Hi DoktorBlue,

Thank You very much for your great help. My problem is solved.

7. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
That is a lot more typing than I would want to do.

If the numeric portion of your values is always 99 or less, you could use
ORDER BY right('0000000000' + [YourField], 10) asc

If this is not the case, and "Numeric values should appear first.", then try
ORDER BY Isnumeric([YourField]) desc, right('0000000000' + [YourField], 10) asc

blindman

8. Registered User
Join Date
Sep 2003
Location
Posts
7
Hi blindman (Guru)

Seems to be simple, but my purpose is not solved. Please see attached excel sheet (zip file) so that you will have idea on how the data is generated

9. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447

blindman, your posting must be mistaken from another thread. Otherwise, you are exposing yourself incapable understanding the problem and the proposed solutions. Why didn't you not even download Mohan's description? Contribute to the thread, or be quiet!

10. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
I inspected your list, and I found three values, that would not be handled correctly by my solution:

479.20 & 478.20
CH162
72.20a

11. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Don't bust a vein Dr. Blue! Forget to take your Prozac today?

Something is interfering with your concentration and your ability to link temporal events, or you'd have noticed that mohan1976 didn't post the attachment until after my post. My solution worked fine with the data that had been posted.

"you are exposing yourself incapable understanding"?

You're starting to get funny again...

Mohan,
Your spreadsheet contains more data, but it doesn't indicate the desired sort order. What do you want to do with data like "479.20 & 478.20", "CH162", and "72.20a"?

Honestly, if you only have to do this once and the Blue Doctor's code works, and you're not going to have to read the code again a year from now and try to figure out what it is doing, then go with Blue's solution.
Otherwise, post some more information on how you want to handle data exceptions, and I'd be curious to know what the data reprents as well.

blindman

12. Registered User
Join Date
Sep 2003
Location
Posts
7
Hi,

479.20 & 478.20 -- not handeled as it is very very rare case and not bothering about it

72.20a is a bad data -- changed to 72.20
CH162 -- wrong entry, so deleted

Sorry for provided the wrong data to the right persons

Basically my data starts with any interger or RR/ or R.R/ or UG/ (Major part) and each values ends with either .10 or .20 or .30 (Minor part) only. If Major part starts with integer then the total value will be integer value only.

One report needs order by Minor part followed by Major part in ascending order (here if major part contains both integer and alphanumeric values then order by integer values first and then the alphanumeric values to be sorted)

In another report order by Major part (here also if major part contains both integer and alphanumeric values then order by integer values first and then the alphanumeric values to be sorted) followed by Minor part in ascending order

Thanks

13. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
If there is any way to do it, I strongly recomment to normalize your table, splitting your code up into three parts (note that what you call Major consists of two parts, a numeric and eventually an alphanumeric portion).

It prevents you from writing complex order clauses, which are indeed difficult to maintain. Therefore, I suggest to put my functional specs as comments into that query.

If you have to use these splitting several times in different queries, consider to use a view, wherein you normalize your field as specified. You can subsequently use the parts within you (easy) order clause. This is actually a variation to my first comment.

14. Registered User
Join Date
Sep 2003
Location
Posts
7
Finally I came to conclution to use

1)To sort by my Minor Part first
SELECT * FROM <Your Table> ORDER BY

2)To sort by whole filed
SELECT * FROM <Your Table> ORDER BY

The above two are solving my purpose as the data 479.20 & 478.20
is made as two seperate values as 479.20 and 478.20

Thanks

15. Registered User
Join Date
Sep 2003
Location
Posts
7
Sorry

for my second, I forgottent to delete the second line in the query while posting here in previous reply. It should be

2)To sort by whole filed
SELECT * FROM <Your Table> ORDER BY