How to order by varchar - 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

General SQL Server Forums

New to SQL Server Programming

How to order by varchar

Author

Topic

oko_sakti
Starting Member

6 Posts

Posted - 2005-12-30 : 02:02:47

Dear all,

I have a varchar field which have data:

ID_trans
---------
T1
T10
T11
T2
T3
everytime i use select statement with order by, the result is above

Does somebody know..? HOw to make the result like below

ID_trans
---------
T1
T2
T3
T10
T11

Sorry i'm not good in english...

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-30 : 02:04:44

Select columns from yourTable Order by len(ID_trans),ID_trans

Madhivanan

Failing to plan is Planning to fail

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-12-30 : 02:13:45

Or else you can even try this

Create table TEST1
(
A varchar(20)
)

Insert into TEST1 values ('T1')
Insert into TEST1 values ('T3')
Insert into TEST1 values ('T11')
Insert into TEST1 values ('T2')
Insert into TEST1 values ('T10')

Select (left(A,1)+ convert(varchar,Substring(A,2,len(A)))) from
TEST1 Order by convert(int,Substring(A,2,len(A)))

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

oko_sakti
Starting Member

6 Posts

Posted - 2005-12-30 : 02:21:04

That's work

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-30 : 02:24:58

>>Select (left(A,1)+ convert(varchar,Substring(A,2,len(A)))) from
TEST1 Order by convert(int,Substring(A,2,len(A)))

No need to use Convert. That will work only if the first charater is Alphabet

Madhivanan

Failing to plan is Planning to fail

oko_sakti
Starting Member

6 Posts

Posted - 2005-12-30 : 02:36:11

That's work

Thank you

oko_sakti
Starting Member

6 Posts

Posted - 2005-12-30 : 02:47:35

What about this

ID_trans
---------
S1
S2
S100
S10
ST1
T1
T10
T11
T100
T2
T3
everytime i use select statement with order by, the result is above

Does somebody know..? HOw to make the result like below

ID_trans
---------
S1
S2
S10
S100
ST1
T1
T2
T3
T10
T11

Thanks

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-12-30 : 03:59:12

There is a function in VB called Val(). This returns only number, if string is passed to it.

I have something equivalent to it below.

Create FUNCTION fnValOnly(@x varchar(8000))
RETURNS int
AS
BEGIN
DECLARE @n int, @MAX_n int, @z varchar(8000)
DECLARE @y table(pos int, value char(1))
SELECT @MAX_n = LEN(@x), @n = 1, @z = ''
WHILE @N<=@MAX_n
BEGIN
INSERT INTO @y(pos,value) SELECT @n, SUBSTRING(@x,@n,1)
SELECT @n = @n + 1
END
select @z =@z +value from @y where isnumeric (value)=1
RETURN @z
END
GO

This is a scalar function and returns int. You can use this function in your query and get the integer separated from character(S and ST), then order by only on number separated and then concatinate.

regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-12-30 : 04:20:13

[code]
CREATE TABLE #t
(
c1 VARCHAR(5)
)
INSERT INTO #t SELECT 'S1'
UNION ALL SELECT 'T3'
UNION ALL SELECT 'S2'
UNION ALL SELECT 'T2'
UNION ALL SELECT 'S10'
UNION ALL SELECT 'S100'
UNION ALL SELECT 'ST1'
UNION ALL SELECT 'T1'
UNION ALL SELECT 'T10'
UNION ALL SELECT 'T11'

SELECT *
FROM #t
ORDER BY LEFT(c1, PATINDEX('%[0-9]%', c1) - 1),
CAST(SUBSTRING(c1, PATINDEX('%[0-9]%', c1),
LEN(c1)-PATINDEX('%[0-9]%',c1)) AS INT)

DROP TABLE #t

c1
-----
S1
S2
S10
S100
ST1
T1
T2
T3
T10
T11

(10 row(s) affected)
[/code]
Consider doing this at the client. PHP, for example, has a handy function "natsort", that will do the trick.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-30 : 04:54:48

>>Consider doing this at the client. PHP, for example, has a handy function "natsort", that will do the trick.

Frank, you seem good in Clent Programming also

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-12-30 : 06:15:26

Not really. I'm doing some stuff in PHP and C++. But that's only for my own. Nothing fancy.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources