select query sorting of 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)

select query sorting of varchar column

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-11-05 : 15:24:51

I am having problem sorting a varchar column, in it i have the following values which are all codes. 1 to 50.

it starts at 1 and skips to 10,11,12,13 etc and then starts 2, 20, 21

How can i sort it. please help.

column name is filecode.

Thank you very much.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-05 : 15:27:51

Select * from mytable a order by convert(int,a.Mycol) asc

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-11-05 : 15:34:39

Hello Vinnie,

Not all the data in this column has numbers it is mixed. it also has alpha numeric plus codes like this too:
1.0
2.0
3.0
4.0
5.0
6.0
1.2.00.1
1.2.00.2
1.2.00.3
RCK002.2
2.TK.234

Will this work if i use convert to Int.
Thank you very much for your help.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-05 : 16:25:30

No, unfortuantly it will not, so on your list how do you want it to sort?? Do you want to sort the numeric portion of the alpha string, or do you want anything with alpha to appear at the end?

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-05 : 16:41:48

It really depends on how you want to sort it.

My suggestion is to FILL 0's to the left until reach max length of the field.

say if your max length is 10, the order is this:

00000001.0
00000002.0
00000003.0
00000010.0
00000011.0
001.2.00.1
001.2.00.2
001.2.00.3
002.TK.234
00RCK002.2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 18:34:56

[code]DECLARE @Sample TABLE
(
data VARCHAR(200)
)

INSERT @Sample
SELECT '10.0' UNION ALL
SELECT '1.0' UNION ALL
SELECT '2.0' UNION ALL
SELECT '3.0' UNION ALL
SELECT '4.0' UNION ALL
SELECT '5.0' UNION ALL
SELECT '6.0' UNION ALL
SELECT '1.2.00.1' UNION ALL
SELECT '1.2.00.2' UNION ALL
SELECT '1.2.00.3' UNION ALL
SELECT 'RCK002.2' UNION ALL
SELECT '2.TK.234'

SELECT data
FROM (
SELECT data,
data + REPLICATE('.', LEN(REPLACE(data, '.', '')) - LEN(data) + 3) AS w
FROM @Sample
) AS q
ORDER BY LEN(PARSENAME(data, 4)),
PARSENAME(data, 4),
LEN(PARSENAME(data, 3)),
PARSENAME(data, 3),
LEN(PARSENAME(data, 2)),
PARSENAME(data, 2),
LEN(PARSENAME(data, 1)),
PARSENAME(data, 1)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources