numeric sort of a varchar field

Question

Post reply

numeric sort of a varchar field

nag.netjob

SSC Rookie

Points: 26
More actions
August 22, 2008 at 10:59 pm

#96520

Hi,
I have a column with datatype nvarchar and it contain some sample data
like:
001
12
112
123
M6921C0014
M6932A0034
M69800034
VALP054
VALP075
VALP086
Now my question is how i achieve sorting on above column?

Viewing 13 posts - 1 through 12 (of 12 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

how about adding an ORDER BY clause to your query?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

nag.netjob (8/22/2008)
Hi,
I have a column with datatype nvarchar and it contain some sample data
like:
001
12
112
123
M6921C0014
M6932A0034
M69800034
VALP054
VALP075
VALP086
Now my question is how i achieve sorting on above column?

Based on the title of this thread, I'm thinking that you left out an awful lot of information... how do you want it sorted? Some of your rows have more than one numeric "field" in them. Please be specific about what you want to sort and how using the data you've already provided. And, if you want a fully tested answer, please take a look at how to submit data for faster help by following/using the methods laid out in the link in my signature.

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

nag.netjob SSC Rookie Points: 26 More actions · Answer 3

Hi,

sorry for the confusion

I have Column datatype in varchar but data contains alpha numerics.

I want show the data in sorting order:

For example sample data like:

12

112

001

when i am doing ascending order i am getting o/p like:

001

112

12

I want output like ascending order like:

001

12

112

I can't convert this datatype to numeric because this column contains both text and numbers.

Thanks

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

na.netjob - you provided some, just some answer to Jeff Moden's question, but NOT enough to allow someone to really help you.

Unanswered is how do you want those values which are mixed numeric and nonnumeric characters sorted. For example with: M6921C0014

Should it be sorted using 6921 the first numeric substring or 0014 the second numeric substring or by 69210014 in which case all numeric characters are utilized in the sorting routine?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

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

bitbucket (8/25/2008)
na.netjob - you provided some, just some answer to Jeff Moden's question, but NOT enough to allow someone to really help you.
Unanswered is how do you want those values which are mixed numeric and nonnumeric characters sorted. For example with: M6921C0014
Should it be sorted using 6921 the first numeric substring or 0014 the second numeric substring or by 69210014 in which case all numeric characters are utilized in the sorting routine?

Spot on, Bit Bucket... exactly correct! Thanks for saving me from more typing! 🙂

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

Michael Meierruth SSChampion Points: 10051 More actions · Answer 6

Could this be what the OP meant?

It's definitely something I have never encountered in the real world.

create table t(c nvarchar(10))

insert into t values('001')

insert into t values('12')

insert into t values('112')

insert into t values('123')

insert into t values('M6921C0014')

insert into t values('M6932A0034')

insert into t values('M69800034')

insert into t values('VALP054')

insert into t values('VALP075')

insert into t values('VALP086')

select c from t

order by right('0000000000'+c,3)

Result:

001

12

M6921C0014

M6932A0034

M69800034

VALP054

VALP075

VALP086

112

123

j nuñez SSCrazy Points: 2175 More actions · Answer 7

Can you try this?

create table #temp (c1 nvarchar(10))

INSERT INTO #temp

SELECT '001'

UNION ALL

SELECT '12'

UNION ALL

SELECT '112'

UNION ALL

SELECT '123'

UNION ALL

SELECT 'M6921C0014'

UNION ALL

SELECT 'M6932A0034'

UNION ALL

SELECT 'M69800034'

UNION ALL

SELECT 'VALP054'

UNION ALL

SELECT 'VALP075'

UNION ALL

SELECT 'VALP086'

select c1 from #temp

where ISNUMERIC(c1) = 1

order by convert(int,c1)

And the results are:

c1

----------

001

12

112

123

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Add this sample data

UNION ALL

SELECT '$1500,0'

and run your query again...

N 56°04'39.16"
E 12°55'05.25"

Michael Meierruth SSChampion Points: 10051 More actions · Answer 9

Peso (8/26/2008)
Add this sample data
UNION ALL
SELECT '$1500,0'
and run your query again...

How about adding a plate of spaghetti and some sushi.:D

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

It will work! 😛

ISNUMERIC('Spagetti') equals to 0.

N 56°04'39.16"
E 12°55'05.25"

Mike Mullen Hall of Fame Points: 3254 More actions · Answer 11

Here is suggestion that uses a case statement in the order by clause.

create table #t(c nvarchar(10))

set nocount on

insert into #t values('001')

insert into #t values('12')

insert into #t values('112')

insert into #t values('123')

insert into #t values('M6921C0014')

insert into #t values('M6932A0034')

insert into #t values('M69800034')

insert into #t values('VALP054')

insert into #t values('VALP075')

insert into #t values('VALP086')

insert into #t values('$1500,0')

select c from #t

order by case when isnumeric(c) = 1 then right('0000000000'+c,10)

else c

end

drop table #t

c

----------

$1500,0

001

12

112

123

M6921C0014

M6932A0034

M69800034

VALP054

VALP075

VALP086

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

All those are well and good, but the OP still hasn't provided an answer to the question of which numeric field (or both) to sort on for some of the data provided.

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