convert to a number datatype for sorting

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

SQL Server Development (2000)

convert to a number datatype for sorting

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-14 : 15:30:55

Hello friends,
I am trying to convert to a NUMERIC datatype for sorting...
My query is:
select IsNull(cat.num,0) + '.' + IsNull(emp.jobnum,0) AS jobcat
from blah blah blah

This is what I did but it didn't work:
select CAST(IsNull(cat.num,0) + '.' + IsNull(emp.jobnum,0) AS NUMERIC)

The data I have is 1.1, 1.2, 1.6, 1.7, I want to have data like this: 1, 1, 2, 2 for easy sorting....
Please help me, guys.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-14 : 15:58:48

As always what didn't work? Did you get an error? Is it not sorting it properly? Always provide enough information so that we can help you. Don't assume we know what is happening on your system as we can't see it or read your mind.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-14 : 16:04:59

Don't know what you want this for


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(num int, jobnum int)
GO

INSERT INTO myTable99(num, jobnum)
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,6 UNION ALL
SELECT 1,7
GO

SELECT ROUND(CONVERT(Decimal(32,2)
	 ,IsNull(CONVERT(varchar(15),num),'0') 
	+ '.' 
	+ IsNull(CONVERT(varchar(15),jobnum),'0')),0) AS jobcat
FROM myTable99


SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-15 : 08:35:54

Woh, It worked.
Thanks so much.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-15 : 09:16:05

Your welcome...and for payment could you just explain why you want to do this?

I mean what's the business reason?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources