Order By - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Order By

Author

Topic

Johan1980
Starting Member

3 Posts

Posted - 2004-03-23 : 16:21:09

Hello there,

I'got a table with a version number (varchar) in many different shapes like the samples here:
7.0
7.0.1
7.0.2
7.0.10
7.1A
8

I want to order this table on this field in the order as above, but it comes up whith the 7.0.10 between the 7.0.1 and the 7.0.2.
I know the reason why he's doing that, but can't get a sollusion to get it right.

Someone got me an idea?

Thanx Johan

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-03-23 : 17:20:36

I see to remember someone on here having the same sort of problem.
I believe that you need to split your string up into it's parts, and then order by the parts (ORDER BY Firstpart, SecondPart, ThirdPart).

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-23 : 19:40:02

You might be able to use the parsename function to break it up easily.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Johan1980
Starting Member

3 Posts

Posted - 2004-03-24 : 04:19:39

yes, taking parts should be the solution, but because of the many difdrent
forms of versionnumbers this isn't easy to.

I still, don't get it right...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-24 : 05:07:54

Hi Johan.
South African Name?

Try this function.
I think it could do the trick.

--****************************************************************

USE NORTHWIND
go
CREATE TABLE MyTable99(Val1 VARCHAR(100))
go
INSERT INTO MyTable99 VALUES('7.0');
INSERT INTO MyTable99 VALUES('7.0.1');
INSERT INTO MyTable99 VALUES('7.0.2');
INSERT INTO MyTable99 VALUES('7.0.10');
INSERT INTO MyTable99 VALUES('7.1A');
INSERT INTO MyTable99 VALUES('8');

go

CREATE FUNCTION ZeroFill(@InpChar VARCHAR(100)) RETURNS VARCHAR(300)
as
BEGIN
DECLARE @TempNoString VARCHAR(10)
DECLARE @CurrChar VARCHAR(1)
DECLARE @RetString VARCHAR(300)
DECLARE @CharCT INT

SET @RetString = ''
SET @TempNoString = ''
SET @CharCT = 1

WHILE @CharCT <= LEN(@InpChar)
BEGIN
SET @CurrChar = SUBSTRING(@InpChar, @CharCT, 1)
IF ISNUMERIC(@CurrChar) = 1 AND @CurrChar <> '.'
BEGIN
SET @TempNoString = @TempNoString + @CurrChar
SET @CurrChar = ''
END
ELSE
BEGIN
IF LEN(@TempNoString) > 0
BEGIN
SET @TempNoString = RIGHT('0000000000' + @TempNoString, 10)
END
SET @RetString = @RetString + @TempNoString + @CurrChar
SET @TempNoString = ''
SET @CurrChar = ''
END
SET @CharCT = @CharCT + 1
END
IF LEN(@TempNoString) > 0
BEGIN
SET @TempNoString = RIGHT('0000000000' + @TempNoString, 10)
END
SET @RetString = @RetString + @TempNoString + @CurrChar

RETURN @RetString
END

go

select * FROM mytable99
order by 1

select *, Northwind.dbo.ZeroFill(Val1)
FROM mytable99
order by Northwind.dbo.ZeroFill(Val1)

--******************************************************************

Duane.

Johan1980
Starting Member

3 Posts

Posted - 2004-03-24 : 08:56:35

Thanx! This works!

I don't no where 'Johan' originally came from, but i'm dutch

Many thanx for this function!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources