order by numeric data in varchar column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

order by numeric data in varchar column

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 18:04:21

Let's say we have a varchar column that stores alphanumeric data. We want to sort it as if it is numeric such as 3, 15, 32, 197 instead of 15, 197, 3, 32.

Here is what the developer currently has in place to fix the numeric sort with a varchar column:

Select incident_key, customer_key, packettrace_key, PacketVersion
from Incident
where customer_key = 4
order by space(50-len(vehicleId))+vehicleId asc

There doesn't appear to be a performance penalty with this order by as I have looked at the execution plan and compared it to other working versions. They all have the same execution plan and same query cost relative to the batch.

Is the order by bad? Is there a better performing order by?

NOTE: the column does contain some alpha characters too, so we can't convert the data type.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-10 : 18:26:38

Are the alpha charectors always in the same location (I.e. Before, or after the Numeric), or can it be mixed (i.e. 1C312). If it is mixed how would you want the following sorted

1x9231
2x9233
15x9203

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 18:36:43

I don't know enough about their data at this point to answer that. I was asked by the developer how to sort in situations like this:

Incorrect sort:
15
197
3
32

Correct order:
3
15
32
197

This is the exact example given to me by the developer. The reason why I wanted to point out that the column contains alpha characters too is in case any solutions here involve converting to int, which would error.

So is there a more efficient way to sort this data other than:
order by space(50-len(vehicleId))+vehicleId asc
?

I tried several different things, but all of them produced the same execution plan plus they all had the same query cost relative to the batch when all run at once.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-10 : 18:47:52

Well the ORDER BY clause is only realy doing this:

ORDER BY LEN(vehicleId), vehicleId ASC

EDIT: I take that back, it is doing a little more, but you still have the same issue with ids of the same length.

Which doens't seem like it would be all that helpful as you still have the same issue with ids of the same length. For example:

DECLARE @Table TABLE (ID VARCHAR(30))

INSERT @Table
SELECT '15b3456' UNION ALL
SELECT '32b3456' UNION ALL
SELECT '12b34567' UNION ALL
SELECT '27b34567' UNION ALL
SELECT '1534567' UNION ALL
SELECT '3b34563' UNION ALL
SELECT '1213456' 

SELECT ID
FROM @Table
ORDER BY SPACE(50 - LEN(ID))+ ID ASC

SELECT ID
FROM @Table
ORDER BY LEN(ID), ID ASC

SELECT ID
FROM @Table
ORDER BY ID ASC

I have to go to another meeting, but I can put in a couple of cycles if you can describe the data a little better. And I may have a trick that may or may not work.. :)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-10 : 19:01:08

There is nothing in the order by that has significant overhead. The way you have it is going to perform better then filtering through the vehicle ID. You're the "Almighty SQL Goddess" :), with 16000 posts, and also I know you helped me out with some difficult issues in the past, it's likley your opinion is probably more accurate than anyone in this forum will be able to give you. Without a large sample, and more details about the sort and if the alpha is mixed with the data, I think it's safe to say that it's as good of a way to order the data as any. Unless you find out specifically what type of AlphaNumeric data is in the vehicleID column, then you might be able to figure out a easy way to convert to int, which will have a possiblilty of slightly outperfoming the current method.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 19:03:05

quote:
Originally posted by Lamprey

I have to go to another meeting, but I can put in a couple of cycles if you can describe the data a little better.

Here are some sample rows:

INSERT @Table
select '621331' union all
select '886496' union all
select '888261' union all
select '888266' union all
select 'C904' union all
select 'C902' union all
select 'C1449' union all
select 'C1519' union all
select 'C1518' union all
select '886477' union all
select 'C1450' union all
select 'C903' union all
select 'MCTDIFFVIN' union all
select '0000176295' union all
select 'CER1.3TEST' union all
select '176295' union all
select '670731' union all
select '670731name' union all
select '777777' union all
select '123456'

I'm just not sure if they only care about sorts when the data is purely numeric. The example I got from him was only numeric data.

These appear to be equivalent:
ORDER BY SPACE(50 - LEN(ID))+ ID ASC
ORDER BY LEN(ID), ID ASC

Is there a more efficient way of doing this? Or are either of the above fine? Let's assume a large result set, perhaps 10000 rows.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 19:06:12

quote:
Originally posted by Vinnie881

You're the "Almighty SQL Goddess" :), with 16000 posts, and also I know you helped me out with some difficult issues in the past, it's likley your opinion is probably more accurate than anyone in this forum will be able to give you.

I disagree with that. My knowledge is mostly on the DBA side of things. I am able to answer a lot of development type questions here, however it certainly is not my expertise.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-10 : 20:20:34

Unfortunatly, I'm not sure how to sort certian circumstances, but this might get you going:

SELECT ID
FROM @Table
ORDER BY 
	CASE 
		WHEN PATINDEX('%[^0-9]%', ID) = 0 THEN LEN(ID) 
		ELSE PATINDEX('%[^0-9]%', ID)
	END,
	LEN(ID), 
	ID

Obviously, it is not perfect but it might give you something to play with. If you have any more specific information on how they want stuff sorted jsut let us know. One other thing that might help, if they only want ids that start with numbers you could add a where clause like:

WHERE PATINDEX('[^0-9]%', ID)

or something.. :)

-Ryan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-10 : 20:41:56

I had an idea.. not sure if it helps...

SELECT 
	ID
FROM 
	@Table
WHERE 
	PATINDEX('[^0-9]%', ID) = 0	
ORDER BY 
	CASE
		WHEN PATINDEX('%[^0-9]%', ID) > 0 THEN LEFT(ID, PATINDEX('%[^0-9]%', ID) - 1)
		WHEN PATINDEX('%[^0]%', ID) > 1 THEN RIGHT(ID, LEN(ID) - (PATINDEX('%[^0]%', ID) + 1))
		ELSE ID
	END

-Ryan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-10 : 20:54:25

Thanks. I will try these out by comparing execution plans and query costs.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Sfumato22
Starting Member

1 Post

Posted - 2007-06-01 : 16:45:05

Try this:

CREATE FUNCTION parseInt(@field varchar(100))
RETURNS int AS
BEGIN
IF (PATINDEX('%[^0-9]%', @field) > 0 ) RETURN(CAST(LEFT(@field, PATINDEX('%[^0-9]%', @field) - 1) AS Int))
ELSE RETURN(CAST(@field AS Int))
RETURN(0)
END

Then just:

SELECT ID
FROM @Table
ORDER BY dbo.parseInt(ID)

excelthoughts
Starting Member

5 Posts

Posted - 2008-06-26 : 22:23:28

I know this is old, but hopefully someone is watching..

I am trying to convert these stored procedures/functions to SQLite.

Can anyone suggest how to do this in SQLite (as it does not have stored procedures)?

CREATE FUNCTION [dbo].[parseInt](@field varchar(100))
RETURNS int AS
BEGIN
IF (PATINDEX('%[^0-9]%', @field) > 0 ) RETURN(CAST(LEFT(@field, PATINDEX('%[^0-9]%', @field) - 1) AS Int))
ELSE RETURN(CAST(@field AS Int))
RETURN(0)
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 22:28:41

quote:
Originally posted by excelthoughts

I know this is old, but hopefully someone is watching..

I am trying to convert these stored procedures/functions to SQLite.

Can anyone suggest how to do this in SQLite (as it does not have stored procedures)?

CREATE FUNCTION [dbo].[parseInt](@field varchar(100))
RETURNS int AS
BEGIN
IF (PATINDEX('%[^0-9]%', @field) > 0 ) RETURN(CAST(LEFT(@field, PATINDEX('%[^0-9]%', @field) - 1) AS Int))
ELSE RETURN(CAST(@field AS Int))
RETURN(0)
END

This is a Microsoft SQL Server forum. Most of us will not be familiar with SQLite. Why don't you try posting over at http://www.sqlite.org/support.html

KH
[spoiler]Time is always against us[/spoiler]

arun0404
Starting Member

2 Posts

Posted - 2010-05-03 : 09:14:40

select MyColumn
from MyTable
order by
case IsNumeric(MyColumn)
when 1 then Replicate(Char(0), 100 - Len(MyColumn)) + MyColumn
else MyColumn
end

arun

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-03 : 12:58:19

arun, I don't even remember this issue, but thanks for the reply three years later!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-03 : 13:17:36

quote:
Originally posted by tkizer

arun, I don't even remember this issue

So I suppose you don't remember the outcome either?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-03 : 13:25:27

quote:
Originally posted by DBA in the making

quote:
Originally posted by tkizer

arun, I don't even remember this issue

So I suppose you don't remember the outcome either?

No idea what the outcome was or even which application it is. I support so many applications and servers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SK2412
Starting Member

2 Posts

Posted - 2014-12-02 : 00:35:25

Hi,

I have a column with values 1,1A,1B,2,11,2A,2B,22,3 so on.

I want to sort this column in this way:
1,1A,1B,11,2,2A,2B,22,3,3A,3B,3C,33,34,4

how can i achieve this?
Please help as this is an urgent issue

Thanks,
SK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-02 : 11:50:24

quote:
Originally posted by SK2412

Hi,

I have a column with values 1,1A,1B,2,11,2A,2B,22,3 so on.

I want to sort this column in this way:
1,1A,1B,11,2,2A,2B,22,3,3A,3B,3C,33,34,4

how can i achieve this?
Please help as this is an urgent issue

Thanks,
SK

Start a new thread.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources