displaying 0 in int - 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

displaying 0 in int

Author

Topic

tendulkar
Starting Member

9 Posts

Posted - 2007-04-25 : 15:56:02

This is what I'm trying to do:

If String AB06 is then I need to get AB05; if it is AB11 I need to get AB10.

My select statement looks like this:

select 'AB'+(cast(substring(period,3,2) as int)-1) from table.

Result is AB6. But I want AB06 (ABzero6)

But when I do this, I'm getting AB6 instead of AB06. Can someone please tell me how to have '0' in front of 6? I guess when I cast it as integer, it is ignoring the leading 0.

Thanks.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-25 : 16:15:24

Try the following:

select 'AB'+(RIGHT('00' + cast(cast(substring(period,3,2) as int)-1 as varchar(2)))) from table.

SQL Server Helper
http://www.sql-server-helper.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-25 : 16:17:10

You need cast the number back to char.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-25 : 16:27:58

What if AB00 is passed in, what gets returned then?

I'm not sure exactly what your larger goal is, but take a look at this:

http://www.sqlteam.com/item.asp?ItemID=26939

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-25 : 16:48:51


select
	Period,
	New_Period = 'AB'+right(right(Period,2)+9999,2)
from 
	(
	select Period = 'AB01' union all
	select Period = 'AB02' union all
	select Period = 'AB04' union all
	select Period = 'AB06' union all
	select Period = 'AB11'
	) a

Results:

Period New_Period 
------ ---------- 
AB01   AB00
AB02   AB01
AB04   AB03
AB06   AB05
AB11   AB10

(5 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources