Alpha before numeric

Question

Post reply

Alpha before numeric

bhudson-1126665

SSC Veteran

Points: 266
More actions
January 15, 2010 at 6:18 am

#386109

I have a dataset that looks like this:
100
101
200
201
300
301
AAA
AAB
AAC
BBA
BBB
BBC
CCA
CCB
etc.
I would like to have the numeric values to come after the alpha. I still want everything ASC. I just want the numeric values to come after the alpha values. I could easily split it into two queries, but I need this to be one query with one dataset.
Any suggestions?

Viewing 6 posts - 1 through 5 (of 5 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

try using ISNUMERIC in the order by

SELECT * FROM yourTable

ORDER BY ISNUMERIC(yourCol), yourCol

bhudson-1126665 SSC Veteran Points: 266 More actions · Answer 2

SSComitted thanks...we're almost there.

That worked. But, I have the added complication of SELECT DISTINCT and it wants me to put isnumeric(search_phrase) in the select statement.

Here's my query...maybe this will help.

SELECT DISTINCT search_phrase FROM chief_search_results WHERE (results > 0) ORDER BY isnumeric(search_phrase), search_phrase

I can't really put isnumeric(search_phrase) in the select statement...can I?

bhudson-1126665 SSC Veteran Points: 266 More actions · Answer 3

Disregard...haven't had my coffee yet...put isnumeric(search_phrase) in the select statement...it works like a champ...thanks a billion!

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

SELECT MyString, ASCII(MyString)

FROM (

SELECT '000' AS MyString UNION ALL

SELECT '101' UNION ALL

SELECT '200' UNION ALL

SELECT '201' UNION ALL

SELECT '300' UNION ALL

SELECT '901' UNION ALL

SELECT ' aAA' UNION ALL

SELECT 'AAB' UNION ALL

SELECT 'AAC' UNION ALL

SELECT 'BBA' UNION ALL

SELECT 'BBB' UNION ALL

SELECT 'BBC' UNION ALL

SELECT 'CCA' UNION ALL

SELECT 'CCB'

) d

ORDER BY CASE WHEN ASCII(MyString) BETWEEN 48 AND 57 THEN 'Number' ELSE 'Alpha' END, MyString

^{“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

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 5

Uh-huh.... and what would you want to do with something like 1d3 and what do you think ISNUMERIC will return for that? Do not use ISNUMERIC as an ISALLDIGITS function because that's simply not what is was designed for. If you want an ISALLDIGITS bit of functionality, you'll need to use LIKE and a double negative...

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

--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)