insert 0 before max no - 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

insert 0 before max no

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-02-07 : 04:17:05

i have a table with the number fields
i have to find the max of the no in sno
QRY ::
select max(sno) from mfrmaster

now if the result is
single digit i have to write as 001
tow digit i have to write as 010

ie:: i have to insert 0 before the max value ...

pls help

Kristen
Test

22859 Posts

Posted - 2006-02-07 : 04:24:08

select RIGHT('000' + CONVERT(varchar(20), max(sno)), 3)
from mfrmaster

Kristen

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-02-07 : 05:52:00

Respected Kristen,
I have got the solution...
Thanks a Lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-07 : 08:09:27

Also, if you use Front End application do this kind of formation there. Here is an example of how to do this in VB6

Format(rs("column"),"000")

Madhivanan

Failing to plan is Planning to fail

sveroa
Starting Member

14 Posts

Posted - 2006-02-08 : 08:07:36

quote:
Originally posted by madhivanan
Here is an example of how to do this in VB6

And C#

int nSno = 1;
System.Console.WriteLine("Sno: " + nSno.ToString().PadLeft(3,'0'));

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-08 : 09:36:37

Back to T-SQL. Another one...


SELECT REPLACE(STR(MAX(sno), 3) , ' ', '0')
  FROM mfrmaster

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 22:16:33

Shortest T-SQL code yet...


select
	right(max(sno)+1000,3)
from
	mfrmaster

CODO ERGO SUM

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-09 : 03:08:48

quote:
Originally posted by Michael Valentine Jones

Shortest T-SQL code yet...

select
right(max(sno)+1000,3)
from
mfrmaster

Nice trick for the lazycoder. Will have to keep that in mind.

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

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 04:27:24

"Nice trick for the lazycoder"

Write-only code costs more when you have to Read it

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-09 : 09:31:49

quote:
Originally posted by Kristen

"Nice trick for the lazycoder"

Write-only code costs more when you have to Read it

Kristen

I was just taking advantage of the implicit conversion to varchar in the RIGHT function.

By the way, all three of the T-SQL solutions posted would have problems with negative numbers, so make sure that a negative sum cannot happen.


select
	X,
	MVJ = right(X+1000,3),
	KRISTEN = right('000'+convert(varchar(20),x),3),
	FRANK = replace(str(x,3) ,' ','0')
FROM
	(select X = -1 union all select -900) a



X           MVJ  KRISTEN FRANK
----------- ---- ------- -----
-1          999  0-1     0-1
-900        100  900     ***

(2 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 09:57:13

So the efficient way is to handle at Client side as I suggested?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 11:41:26

"problems with negative numbers"

In the spirit of Write-Only code we'll just throw an ABS() around it then, eh? I mean ... seeing as Negative Numbers can't happen and all that ...

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-09 : 11:52:24

quote:
Originally posted by madhivanan

So the efficient way is to handle at Client side as I suggested?

Madhivanan

Failing to plan is Planning to fail

Client side or server side, you always have to be aware of the edge conditions and decide what you want to do.

Did you check the client side code you posted for -999?

For example:


select
	X,
	MVJ =
	case when x between 0 and 999
	then right(X+1000,3)
	when x between -99 and -1
	then '-'+right(X+(sign(x)*1000),2)
	else null end
from
	(
	select X = 1
	union all
	select 999
	union all
	select 1000
	union all
	select -1
	union all
	select -99
	union all
	select -999
	) a

X           MVJ  
----------- ---- 
1           001
999         999
1000        NULL
-1          -01
-99         -99
-999        NULL

(6 row(s) affected)

CODO ERGO SUM

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-10 : 04:03:42

quote:
Originally posted by Kristen

"problems with negative numbers"

then, eh? I mean ... seeing as Negative Numbers can't happen and all that ...

Hm, think I need to tell that my bank next time.

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

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 05:56:21

Isn't a Credit a negative number though, as far as the bank is concerned?

I can never remember - when I was brought up the Credits were the ones in the ledger which were nearer the window!

Kristen

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-10 : 07:21:56

quote:

when I was brought up the Credits were the ones in the ledger which were nearer the window!

Uhoh, language barrier! I don't understand you here. Care to explain?

--
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 - 2006-02-10 : 07:50:10

MVJ, If you use VB6
format(-1,"000")=>-001
format(-21,"000")=>-021
format(-999,"000")=>-999

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 13:07:03

"language barrier"

OK: Imagine the old days when accounts were kept in a ledger book, which was on a lectern with ink pot and quill pen!

The art of learning Credits and Debits is hard - so the old boy that taught me simply said "The Credits are in the column nearest the window"

Kristen

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-13 : 04:11:22

Thanks, I understand now.

quote:

The art of learning Credits and Debits is hard

Nice wording, btw. I rather would have used "Dilbert language", when speaking of Accountants and Accounting.

--
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