Stripping the leading number and sort in order

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

Stripping the leading number and sort in order

Author

Topic

Rayman
Starting Member

39 Posts

Posted - 2010-10-26 : 12:29:16

This script is working.
******************************************************************
SCRIPT:
Select database_name as [database name],

datename(month,database_date) as [ month ],

CASE WHEN datepart(hour,database_date) <12 THEN 'YES' ELSE 'NO' END as AM,

CASE WHEN datepart(hour,database_date) >=12 THEN 'YES' ELSE 'NO' END as PM,

convert(decimal(6,0),datebase_size)'database size',

CASE WHEN CHARINDEX('SQL2000', [datebase_version]) > 0 THEN 'YES' ELSE 'NO' END as [SQL 2000],

CASE WHEN CHARINDEX('SQL2005', [datebase_version]) > 0 THEN 'YES' ELSE 'NO' END as [SQL 2005]

FROM JOHN;
*******************************************************************

Terry Lynn King

Rayman
Starting Member

39 Posts

Posted - 2010-10-26 : 12:31:53

Raw Data

database_name datatbase_date database_version database_size database Number 1 11/30/08 1:52 AM SQL2000 ver 1.0 54.5
database 2 12/31/08 9:22 PM SQL2005 ver 2.11 2254.78
database 3 3/17/08 11:22 AM SQL2000 ver 1.023 12.4
database 143 1/4/09 11:22 PM SQL2005 ver 3.002 234.32

quote:
Originally posted by Rayman

This script is working.
******************************************************************
SCRIPT:
Select database_name as [database name],

datename(month,database_date) as [ month ],

CASE WHEN datepart(hour,database_date) <12 THEN 'YES' ELSE 'NO' END as AM,

CASE WHEN datepart(hour,database_date) >=12 THEN 'YES' ELSE 'NO' END as PM,

convert(decimal(6,0),datebase_size)'database size',

CASE WHEN CHARINDEX('SQL2000', [datebase_version]) > 0 THEN 'YES' ELSE 'NO' END as [SQL 2000],

CASE WHEN CHARINDEX('SQL2005', [datebase_version]) > 0 THEN 'YES' ELSE 'NO' END as [SQL 2005]

FROM JOHN;
*******************************************************************

Terry Lynn King

Terry Lynn King

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-26 : 12:34:17

Don't open so many threads please.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151923

No, you're never too old to Yak'n'Roll if you're too young to die.

Rayman
Starting Member

39 Posts

Posted - 2010-10-26 : 12:35:26

I understand the goal is to place the database Number in order like this, I can not correct the table, I need to drop the leading 1 in the number 143 to allow the field to state the last entry is 43. SO I am looking to trunc the 1 in 143 to represent 43.

Database Name Database number
database number 1 1
database 2 2
database 3 3
database 143 43

quote:
Originally posted by Rayman

Raw Data

database_name datatbase_date database_version database_size database Number 1 11/30/08 1:52 AM SQL2000 ver 1.0 54.5
database 2 12/31/08 9:22 PM SQL2005 ver 2.11 2254.78
database 3 3/17/08 11:22 AM SQL2000 ver 1.023 12.4
database 143 1/4/09 11:22 PM SQL2005 ver 3.002 234.32

quote:
Originally posted by Rayman

This script is working.
******************************************************************
SCRIPT:
Select database_name as [database name],

datename(month,database_date) as [ month ],

CASE WHEN datepart(hour,database_date) <12 THEN 'YES' ELSE 'NO' END as AM,

CASE WHEN datepart(hour,database_date) >=12 THEN 'YES' ELSE 'NO' END as PM,

convert(decimal(6,0),datebase_size)'database size',

CASE WHEN CHARINDEX('SQL2000', [datebase_version]) > 0 THEN 'YES' ELSE 'NO' END as [SQL 2000],

CASE WHEN CHARINDEX('SQL2005', [datebase_version]) > 0 THEN 'YES' ELSE 'NO' END as [SQL 2005]

FROM JOHN;
*******************************************************************

Terry Lynn King

Terry Lynn King

Terry Lynn King

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-26 : 15:30:24

Do you have a question?

If so, please make some effort to ask it, including a clear explanation of what you need to do, sample data, and sample output.

CODO ERGO SUM

Rayman
Starting Member

39 Posts

Posted - 2010-10-26 : 16:29:00

The script posted works and provide the clear input. I am trying to add to the script the ability to include a one or two line addition to gather the information in the database name column, sort by number to represent the following output

Database Name Database number
database number 1 1
database 2 2
database 3 3
database 143 43

The current script produces this output
database number 1 November YES NO 55 YES NO
database 2 December NO YES 2255 NO YES
database 3 March YES NO 12 YES NO
database 143 January NO YES 234 NO YES

The desired output includes the number of the database in the second column under the *
database number 1 1 November YES NO 55 YES NO
database 2 2 December NO YES 2255 NO YES
database 3 3 March YES NO 12 YES NO
database 143 43 January NO YES 234 NO YES

quote:
Originally posted by Michael Valentine Jones

Do you have a question?

If so, please make some effort to ask it, including a clear explanation of what you need to do, sample data, and sample output.

CODO ERGO SUM

Terry Lynn King

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-26 : 17:34:03

select
	*,
	DB_Num =convert(int,reverse(substring(reverse(DB),1,charindex(' ',reverse(DB))-1)))%100

from
	( -- Test data
	select DB = 'database 1'	union all
	select DB = 'database 143'	union all
	select DB = 'database 44'
	) b
order by
	DB_Num

Results:

DB           DB_Num      
------------ ----------- 
database 1             1 
database 143          43 
database 44           44

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources