How do we convert text to numeric in T-SQL statement to sort numeric ascending...........

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)

How do we convert text to numeric in T-SQL statement to sort numeric ascending...........

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-05 : 08:01:54

Michael writes "Select APP_HULL, BOOKNUM, APP, BOOKREV
FROM CABLES
ORDER BY APP_HULL, BOOKNUM, DWGSHT ASC

Above is my t-SQL statement using SQL 7.0.
DWGSHT is a varchar datatype. I need the results to sort like this:
1
2
3
4
5
6A
6B
6C
7

and not like:
1
10
11
12
.
.
19
2
20
21

I tried CAST and CONVERT to convert DWGSHT to int. It failed because of cases where DWGSHT = '6A' or '1093A'.

Your assistance is greatly appreciated!

Michael Sargent
Programmer Analyst"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-05 : 08:51:53

it would be useful if you could pad the number portion of DWGSHT out to the same length, then your problem would go away.....

but something similiar may be possible on the fly via a case statement in the order by phrase....

padding/leading zeros came up here before.....a search may point you in the right direction.

Jay99

468 Posts

Posted - 2002-04-05 : 09:44:29

you could follow this thread to find out about parsing character values out . . .

Jay
<O>

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-08 : 15:18:31

A simple way to pad leading zeroes, as Andrew suggests, is to concatenate a string of 0's to the beginning of your other field, and then take the Right X number of characters. Something like this:

USE PUBS
SELECT right('0000000000' + ZIP, 8)
FROM Authors

This gets you the zip (which is a char(5) field) and leading zeroes, padded to a total of 8 characters.

HTH

------------------------
GENERAL-ly speaking...

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 15:30:14

nice solution Ajarn.

Just to help out Michael even more


Select APP_HULL, BOOKNUM, APP, BOOKREV 
FROM CABLES 
ORDER BY APP_HULL, BOOKNUM, Right('00000000' + DWGSHT, 8) ASC

nr
SQLTeam MVY

12543 Posts

Posted - 2002-04-08 : 20:23:10

Do you have a max of 1 letter after the number?

order by
case when isnumeric(right(DWGSHT,1)) = 1
then right(space(10) + DWGSHT,10)
else right(space(10) + left(DWGSHT,len(DWGSHT)-1),10)
end

If you have more letters then just add more case statements.
You could also use a convert int on the remainder if you prefer.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources