SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 order by numeric data in varchar column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tkizer
Almighty SQL Goddess

USA
36907 Posts

Posted - 04/10/2007 :  18:04:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 04/10/2007 :  18:26:38  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36907 Posts

Posted - 04/10/2007 :  18:36:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 04/10/2007 18:38:21
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/10/2007 :  18:47:52  Show Profile  Reply with Quote
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.. :)

Edited by - Lamprey on 04/10/2007 18:49:30
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 04/10/2007 :  19:01:08  Show Profile  Reply with Quote
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.



Edited by - Vinnie881 on 04/10/2007 19:03:40
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36907 Posts

Posted - 04/10/2007 :  19:03:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36907 Posts

Posted - 04/10/2007 :  19:06:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/10/2007 :  20:20:34  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/10/2007 :  20:41:56  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36907 Posts

Posted - 04/10/2007 :  20:54:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
Thanks. I will try these out by comparing execution plans and query costs.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Sfumato22
Starting Member

1 Posts

Posted - 06/01/2007 :  16:45:05  Show Profile  Reply with Quote
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)

Edited by - Sfumato22 on 06/01/2007 17:15:14
Go to Top of Page

excelthoughts
Starting Member

5 Posts

Posted - 06/26/2008 :  22:23:28  Show Profile  Visit excelthoughts's Homepage  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 06/26/2008 :  22:28:41  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

arun0404
Starting Member

India
2 Posts

Posted - 05/03/2010 :  09:14:40  Show Profile  Reply with Quote
select MyColumn
from MyTable
order by
case IsNumeric(MyColumn)
when 1 then Replicate(Char(0), 100 - Len(MyColumn)) + MyColumn
else MyColumn
end


arun
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36907 Posts

Posted - 05/03/2010 :  12:58:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 05/03/2010 :  13:17:36  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36907 Posts

Posted - 05/03/2010 :  13:25:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000