Order by Alphanumeric 1

[smain]

I need to sort a varchar(10) field containing the following values...

1.7.1.3
12.15.1
1.5.3.2
1.1
1
5.1
5.10.3
5.1.3

into the following results...
1
1.1
1.5.3.2
1.7.1.3
5.1
5.1.3
5.10.3
12.15.1

The source table only has about 1,700 records, with 900+ uniqe values.

 

[mrdenny]

The first thing that comes to mind would be breaking the strings apart into 4 columns filling in a 0 if there is no data, then sorting by that. It wouldn't be very efficient but it would work.

You would be using charindex and substring to break it apart. I'd personally do it all in the order by clause.

Something like:

order by case when CHARINDEX('.', FieldName, 0) = 0 then
   convert(int, FieldName) 
else 
    convert(int, substring(FieldName, 0, charindex('.', FieldName, 0)) 
end,
convert(int, substring(FieldName, substring(FieldName, 0, CHARINDEX('.', FieldName, 0), charindex('.', FieldName, 0))

Keep in mind that I haven't actually tested this, so you may need to play with it a little bit.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Walid]

I just posted a function in FAQ that may solve this problem. Please read faq183-6095

Thanks


Walid Magd

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[smain]

Thanks to both of you. Walid, the function works great.

 

[SQLDenis]

No need to go thru all that trouble, take a look at the code below, you just need order by convert(int,replace(field,'.',''))

create table Menace (field varchar(50))
insert into Menace
select '1.7.1.3' union all
select '12.15.1' union all
select '1.5.3.2' union all
select '1.1' union all
select '1' union all
select '5.1' union all
select '5.10.3' union all
select '5.1.3'

select * from Menace
order by convert(int,replace(field,'.',''))

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Walid]

With all due respect, the trouble is needed. Your solution assumes that the data will always be stored a certain way and will return the expected results only if it is stored that way, while mine is generic and always will return the correct answer.

For example we thought, one day, we are ok with one digit assigned to the minor (least significant) segment of the version number and then we had to expand it to accommodate our needs. In that situation your SQL statement is just a bug waiting to happen as I will explain in the following code.

It is the same code as yours with 5.10.3 changed to be 5.10.03

CREATE TABLE Menace2 (field VARCHAR(50))
GO
INSERT INTO Menace2
SELECT '1.7.1.3' UNION ALL
SELECT '12.15.1' UNION ALL
SELECT '1.5.3.2' UNION ALL
SELECT '1.1' UNION ALL
SELECT '1' UNION ALL
SELECT '5.1' UNION ALL
SELECT '5.10.03' UNION ALL
SELECT '5.1.3'
GO
SELECT * FROM Menace2
ORDER BY CONVERT(INT,REPLACE(field,'.',''))
GO

field
--------------------------------------------------
1
1.1
5.1
5.1.3
1.5.3.2
1.7.1.3
12.15.1
5.10.03

When the data was stored as 5.10.3, the sort order was 5.10.3 then 12.15.1 but now it is the wrong order. On the other hand
SELECT dbo.PadLEx(field, 3,2,'.','0') as a from Menace2 order by a

Will put 5.10.03 before 12.15.1 which is the correct sort order.

Also your technique will not answer this question correctly

IF convert(int, replace('10.1.3','.','')) > convert(int, replace('2.10.20','.',''))
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

Because the result of the above code is false!!

While
IF dbo.PadLEx('10.1.3', 3,2,'.','0') > dbo.PadLEx('2.10.20', 3,2,'.','0')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

Will give you the correct answer



Walid Magd

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[SQLDenis]

I understand that, I was looking at THIS data specifically
If they are always int (meaning no leading zeros) then my solution is fine

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

What about some circus art? :

select field 
from Menace
order by convert(int, reverse(parsename(reverse(field), 1))),
	 convert(int, reverse(parsename(reverse(field), 2))),
	 convert(int, reverse(parsename(reverse(field), 3))), 
	 convert(int, reverse(parsename(reverse(field), 4)))

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

vongrunt, yes this is Iztik Ben-Gan solution (SQL Server magazine)
I am trying to get it another way somehow

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

Then damnit... I thought donutman would be proud of me .

Btw. it can't be done with simple REPLACE() because tokens are variable-length.

Idea: shall we open some kind of math challenge? Stars for most original anwsers or something?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

well here is mine, not very elegant

CREATE TABLE Menace3 (field VARCHAR(50))
GO
INSERT INTO Menace3
SELECT '1.7.1.3' UNION ALL
SELECT '12.15.1' UNION ALL
SELECT '1.5.3.2' UNION ALL
SELECT '1.1' UNION ALL
SELECT '1' UNION ALL
SELECT '5.1' UNION ALL
SELECT '5.10.03' UNION ALL
SELECT '5.01.30'
GO
SELECT * FROM Menace3
ORDER BY CONVERT(INT,REPLACE(REPLACE(field,'0','.'),'.','')),
len(field),CONVERT(INT,(REPLACE(field,'.','')))
GO

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

This returns:

1
1.1
5.1
5.01.30
5.10.03
1.5.3.2
1.7.1.3
12.15.1


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Oops pasted the wrong code

drop table Menace3
CREATE TABLE Menace3 (field VARCHAR(50))
GO
INSERT INTO Menace3
SELECT '1.7.1.3' UNION ALL
SELECT '12.15.1' UNION ALL
SELECT '1.5.3.2' UNION ALL
SELECT '1.1' UNION ALL
SELECT '1' UNION ALL
SELECT '5.1' UNION ALL
SELECT '5.01.00003' UNION ALL
SELECT '5.01.30'
GO
SELECT *
FROM Menace3
ORDER BY CONVERT(INT,REPLACE(REPLACE(field,'0','.'),'.','')),
len(REPLACE(field,'0',''))
GO

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

Same thing Check results...

Interesting problem... I'd also like to avoid custom UDFs and PARSENAME() if possible.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

????
results pasted
1
1.1
5.1
5.01.00003
5.01.30
1.5.3.2
1.7.1.3
12.15.1


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

Shouldn't 5.* values appear after 1.7.1.3 ?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

vongrunt, so I went a little crazy with this thing

drop table Menace3
CREATE TABLE Menace3 (field VARCHAR(50))
GO
INSERT INTO Menace3
SELECT '1.7.1.3' UNION ALL
SELECT '12.15.1' UNION ALL
SELECT '1.5.3.2' UNION ALL
SELECT '1.1' UNION ALL
SELECT '1' UNION ALL
SELECT '5.1' UNION ALL
SELECT '5.1.03' UNION ALL
SELECT '5.1.30'
GO
SELECT *
FROM Menace3
ORDER BY convert(int,replace(left(field,patindex('%.%',field)),'.','')),
CONVERT(int,REPLACE(field,'.',''))

1
1.1
1.5.3.2
1.7.1.3
5.1
5.1.03
5.1.30
12.15.1



“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

Try again with this:

... UNION ALL
SELECT '1.01' UNION ALL
SELECT '5.1.4'
...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)