put '0' to number 1-9 - 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

SQL Server 2000 Forums

Transact-SQL (2000)

put '0' to number 1-9

Author

Topic

icebo3
Starting Member

20 Posts

Posted - 2003-09-24 : 03:12:45

in sql server, what is the sql script or function to assign a '0' to number from 1-9 ?? example when output is 2 is should change it to 02. if 12 then it is 12

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-09-24 : 04:38:54

AFAIK, there is no inbuilt function. we have to write our own UDF
HTH..


use tempdb
go
Create FUNCTION CONV_INT (@intNum numeric(10))
returns varchar(10)
AS
Begin
declare @retstr varchar(10)
	if @intNum<10
	begin
		set @retstr = '0' + cast(@intNum as char)
	end
	else
	begin
		set @retstr = cast(@intNum as char)
	end
return (@retstr)
End
go
select tempdb.dbo.conv_int(9)

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-24 : 05:22:02

declare @len integer
set @len = 6

select right(replicate('0',@len) + convert(varchar,customercode)),@len)
from customertable

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-09-24 : 07:08:39

quote:
Originally posted by AndrewMurphy

declare @len integer
set @len = 6

select right(replicate('0',@len) + convert(varchar,customercode)),@len)
from customertable

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-24 : 09:09:19

Always do this at the presentation layer.

where are you returning to data to ? ASP, VB, HTML, etc ???

Use the presenation layer to do your formatting. SQL's job is to return the DATA. The presentation layer's job is to PRESENT and FORMAT the data so that it looks nice.

- Jeff

icebo3
Starting Member

20 Posts

Posted - 2003-09-24 : 20:37:54

i am just writing a simple sql script to get gather date data. as this are records are month (1- 12), i need to sort it by the number, but it will be 1,11,12,2,3,4,5... so that i need tosort it ...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-24 : 21:02:03

CONVERT() to an integer, then. look up the convert function in books on-line.

- Jeff

icebo3
Starting Member

20 Posts

Posted - 2003-09-24 : 21:49:14

i have been trying the convert() function but failed ...

how to "select convert(__,'1',__)" and output is '01'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-24 : 22:00:21

SELECT .... whatever
ORDER BY Convert(int, col1)

Ya know, this would be a lot easier if you posted some actual table structures, actual data, and actual results you need to see. Your post about ORDER BY doesn't require the leading zero's to be displayed at all.

icebo3
Starting Member

20 Posts

Posted - 2003-09-24 : 22:56:13

let me try to explain better ... i am using sql server to write a script. this script will then be put to a program called Business Object to run.

i have a period table that store the month. so i will pull the month(column data type is smallint) out from period table. so the output will be 1 to 12 . as i need to put format of "M1", "M2" as in the 2 nd month of the year then i need to varchar the 1-12 so that i can join with "M" (M & varchar 1-12) but in Business Object this is treat as varchar and when sorted it will be "M1","M10","M11","M12","M2" .... "M9" so before i join 1-12 to "M", i need to conevrt 1-12 to 01,02....12 so that Business Object can sort it properly
and then in Business Object i will sort the "Mo7"

icebo3
Starting Member

20 Posts

Posted - 2003-09-24 : 23:09:58

for better example, you can use the pubs database in sql server and the table dbo.discounts

u can treat the discount column as the month i am looking for. and ignore the decimal behind

select 'M' + cast(discount as varchar) from dbo.discounts order by 1. when sorted in Business Object, it will be M10.50,M5.00,M6.70 but actually i want to be
M5.00
M6.70
M10.50

later i will run this script in Business Object and Business Object will sort this month..

so in the script level i will try to assign a '0' to number from '1-9'

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-09-25 : 01:49:48

Try this..


select 
      'M' + cast(discount as varchar) 
from 
      dbo.discounts 
order by 
      convert(int,discount)

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

icebo3
Starting Member

20 Posts

Posted - 2003-09-25 : 03:24:26

yes this select
'M' + cast(discount as varchar)
from
dbo.discounts
order by
convert(int,discount)
work in sql level but in Business Object level, Business Object does not follow that order by in sql level but will but sorted by Business Object

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-09-25 : 07:33:02

quote:
work in sql level but in Business Object level, Business Object does not follow that order by in sql level but will but sorted by Business Object

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-25 : 09:52:34

Why not build a view and reference that?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

icebo3
Starting Member

20 Posts

Posted - 2003-09-28 : 21:56:16

good idea with the view ....

in sql level we can use the "order by convert(int,discount)" but in BO, the value "M." is capture in BO and will treat it as character, so when it is sorted, it will according to chracter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources