Order by Numeric value of a Varchar column Need a better solution

Question

Order by Numeric value of a Varchar column Need a better solution

mail2dinesh.sharma

SSC Veteran

Points: 245
More actions
April 20, 2008 at 12:15 pm

#380075

I have a column called TradeReference which mostly contains Numeric value (for referring the Trade orders). But it may occasionally contain AlphaNumeric values.The values in this column is important and represent the order in which the Trade was executed.
Now here is the problem. Let us say I have 2 trade records with the following values in that column.
080232323434
332234532
If I select record with order by clause for that column then the record with Trad Ref 33322323 appears first in output which is not what I wanted. Since the Record with reference 080232323434 has numeric value higher than 332234532 it must appear in the selected set. So in order to change its sorting strategy by TSQl(which is doing string comparison of each character) I can do the following way
select cast(TradeRef as integer) TRef , order by TRef
So converting it into number would solve the problem. But then I also have to satisfy another requirement , of supporting Alpha Numeric values. The moment an alpha numeric value is encountered the TSQl throws error that it cannot convert the field value. Is there a better solution to deal with this situation ?
~D

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

how about adding a "calculated" column wich contains the actual sort-value.

This way it will take some space, but you can index the column and you don't have to figure it out every time you want to sort on it.

i.e.

case when isnumeric(the_ref ) or isnull(the_ref,'') = ''

begin

replicate('0', 16 - len(ltrim(rtrim(the_ref )))) + the_ref

end

else

the_ref

end

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Nope... ISNUMERIC treats $ signs, decimal points, and a whole bunch of other non-digit characters as if they were numeric. Never use ISNUMERIC to try to create an IsAllDigits criteria.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Dinesh,

How about providing some additional samples where the TradeReference column contains the Alpha characters you're talking about?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

I believe the simplest way would be padding numeric values with spaces to make them all equal in length.

Functiuon STR would be helkpful for this.

Check BOL for options of this fuction.

Then '5' will be ' 5', '100' will be ' 100', and SQL Server will order your lines correctly.

_____________
Code for TallyGenerator

mail2dinesh.sharma SSC Veteran Points: 245 More actions · Answer 5

I am not sure if the padding helps. Because I need to know the length of Trade Reference field in order to pad the field. This is varchar field of 32 size . So do you want me, to pad for all 32 positions. I may sound wrong or stupid please correct me.

mail2dinesh.sharma SSC Veteran Points: 245 More actions · Answer 6

333233343MVF is such example. Until now I have seen only alphabets and numbers but not any special characters. Beside the value I receive is from an external source so I am not completely aware of the range or type of values I get in that field

~D

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Here's why I say NOT to use ISNUMERIC as an "IsAllDigits" function...

[font="Courier New"] SELECT ISNUMERIC('143563d21'),

ISNUMERIC('143563e35')

----------- -----------

1 1

(1 row(s) affected)[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

mail2dinesh.sharma (4/20/2008)
333233343MVF is such example. Until now I have seen only alphabets and numbers but not any special characters. Beside the value I receive is from an external source so I am not completely aware of the range or type of values I get in that field
~D

So, what is it that you actualy want to sort on? Just the 333233343?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 9

Jeff Moden (4/20/2008)
Here's why I say NOT to use ISNUMERIC as an "IsAllDigits" function...

Indeed, I forgot about the currency signs. :crying:

Thank you for providing the faulty example.

They'd better remove ISNUMERIC from the lexicon :doze:

or fix it the correct way.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Have you tried Sergiy's suggestion?

SELECT * FROM (

SELECT RIGHT(SPACE(32) + RTRIM(LTRIM(' 1 ')), 32) AS TradeReference UNION ALL

SELECT RIGHT(SPACE(32) + RTRIM(LTRIM('12345678901234567890123456789012')), 32) UNION ALL

SELECT RIGHT(SPACE(32) + RTRIM(LTRIM('080232323434')), 32) UNION ALL

SELECT RIGHT(SPACE(32) + RTRIM(LTRIM('332234532')), 32) UNION ALL

SELECT RIGHT(SPACE(32) + RTRIM(LTRIM(CAST(NULL AS VARCHAR(32)))), 32)

) d ORDER BY TradeReference

NULL

1

332234532

080232323434

12345678901234567890123456789012

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

RyanRandall SSChampion Points: 13623 More actions · Answer 11

I feel that we haven't quite pinned your requirements down yet. Maybe the sample data and query here will help...?

declare @t table (TradeRef varchar(32))

insert @t

select '080232323434'

union all select '332234532'

union all select '33322323'

union all select '5'

union all select '100'

union all select '333233343MVF'

union all select '143563d21'

union all select '143563e35'

union all select '333233343'

union all select '1'

union all select '12345678901234567890123456789012'

union all select 'ABC123DEF'

union all select 'AAAA11BBBBB2222'

union all select 'AAAA'

union all select null

union all select ''

union all select ' '

union all select 'A '

union all select ' A'

select TradeRef, Number from (

select *, nullif(substring(TradeRef, iStart, patindex('%[^0-9]%', substring(TradeRef, iStart, 32) + 'a')-1), '') as Number

from (select *, patindex('%[1-9]%', TradeRef) iStart from @t) a) a

order by case when Number is null then 'a' + isnull(TradeRef, '') else right(space(32) + Number, 32) end

/*

TradeRef Number

-------------------------------- --------------------------------

1 1

5 5

AAAA11BBBBB2222 11

100 100

ABC123DEF 123

143563d21 143563

143563e35 143563

33322323 33322323

332234532 332234532

333233343MVF 333233343

333233343 333233343

080232323434 80232323434

12345678901234567890123456789012 12345678901234567890123456789012

NULL NULL

NULL

A NULL

AAAA NULL

*/

If you wouldn't want this sample data in this order, tell us what order you would want it in and why... 🙂

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

ALZDBA (4/21/2008)
Jeff Moden (4/20/2008)
Here's why I say NOT to use ISNUMERIC as an "IsAllDigits" function...
Indeed, I forgot about the currency signs. :crying:
Thank you for providing the faulty example.
They'd better remove ISNUMERIC from the lexicon :doze:
or fix it the correct way.

No, no... it DOES work correctly. The examples can all be converted to some form of numeric data type including the ones I just showed which are an engineering type of notation. ISNUMERIC simply means a conversion from a character based data type to a numeric data type can occur. It does not mean nor has it ever been advertised to mean "IsAllDigits".

"IsAllDigits" can easily be accomplished by using...

WHERE somecolumn NOT LIKE '%[^0-9]%'

... where the "^" stands for NOT. It's a "double-not" and it works great.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

intvfan SSChasing Mays Points: 628 More actions · Answer 13

The way I order varchar/char columns with numeric data is to order by len and then data.

ie select x from table order by len(x),x

then the results are in the correct order.

you do not get: You do get:

111 2

2 3

3 9

77878758587 111

9 77878758587

Maybe this will help you?

It is not inline, but it may solve your problem?

Gene

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 14

Jeff Moden (4/21/2008)

No, no... it DOES work correctly. The examples can all be converted to some form of numeric data type including the ones I just showed which are an engineering type of notation. ISNUMERIC simply means a conversion from a character based data type to a numeric data type can occur. It does not mean nor has it ever been advertised to mean "IsAllDigits".
"IsAllDigits" can easily be accomplished by using...
WHERE somecolumn NOT LIKE '%[^0-9]%'
... where the "^" stands for NOT. It's a "double-not" and it works great.

I must learn not to go on a forum before my first coffee :blush: :Whistling:

Especially on a Monday ... :hehe:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me