Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

string formatting Expand / Collapse
Author
Message
Posted Thursday, October 30, 2008 8:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:57 PM
Points: 3, Visits: 82
in Access, you can use the following sql statement to format a number:
format(number,"0000") as result
An example of result could be 0023, rather than 23
WHen doing in SQL Server, I am advised that format is not a recognised function. WHat can I do to achieve the same result in SQL Server? Cheers.
Post #594387
Posted Thursday, October 30, 2008 8:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
I don't know of a function in SQL that will format a number that way. I generally do something like this.

Declare @i int
Set @i = 23

Select Right('0000' + Cast(@i as varchar(4)),4)


Ken Simmons
http://twitter.com/KenSimmons
Post #594398
Posted Thursday, October 30, 2008 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:57 PM
Points: 3, Visits: 82
thanks Ken

it worked perfectly.
Post #594421
Posted Thursday, October 30, 2008 10:45 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 2:01 PM
Points: 659, Visits: 6,104
This type of situation is encountered frequently (see Dave's post re: Converting Numeric to String http://www.sqlservercentral.com/Forums/Topic594180-8-1.aspx )

The approaches listed there and in the previous reply work, but for singular occurences of this type of query. I creaeted the two following UDF's that in any situation allow formatting of decimal or integer values with a given "pad" character.

create function dbo.formati(@value int, @pad int, @padchar char(1) )
returns varchar(max)
as
begin
return (select REPLICATE( @padchar ,@pad-len(@value)) + convert(varchar,@value) )
end
go

create function dbo.formatd(@value decimal(10,2), @pad int, @padchar char(1) )
returns varchar(max)
as
begin
return (select REPLICATE( @padchar ,@pad-len(@value)) + convert(varchar,@value) )
end
go

select dbo.formati( MONTH('2008-09-01'),2, '0' )
select dbo.formati( 123,5, '0' )
select dbo.formatd( 123.45,8, 'x' )

Too bad you can't overload functions in T-SQL and use just one function call and let the engine chose the correct function based on the signature (hint hint, MS :) )

DAB
Post #594541
Posted Friday, October 31, 2008 3:19 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 405, Visits: 1,136
Thanks DAB! That's very useful to know. :D

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #594832
Posted Friday, October 31, 2008 4:36 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 405, Visits: 1,136
DAB

i've just tried to implement this on an SQL2000 database, but I getting an error on both functions:

Msg 170, Level 15, State 1, Procedure formati, Line 2
Line 2: Incorrect syntax near 'max'.
Msg 178, Level 15, State 1, Procedure formati, Line 6
A RETURN statement with a return value cannot be used in this context.


Msg 170, Level 15, State 1, Procedure formatd, Line 3
Line 3: Incorrect syntax near 'max'.
Msg 178, Level 15, State 1, Procedure formatd, Line 7
A RETURN statement with a return value cannot be used in this context.


any ideas?


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #594856
Posted Friday, October 31, 2008 6:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
mikemireku (10/30/2008)
in Access, you can use the following sql statement to format a number:
format(number,"0000") as result
An example of result could be 0023, rather than 23
WHen doing in SQL Server, I am advised that format is not a recognised function. WHat can I do to achieve the same result in SQL Server? Cheers.
Where do you want to show data?
If you use front end application, you can use format function there




Madhivanan

Failing to plan is Planning to fail
Post #594919
Posted Friday, October 31, 2008 7:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:59 AM
Points: 803, Visits: 1,195
Hi Dave,

VARCHAR(MAX) was only introduced in SS2K5, so you won't be able to use it in 2K.

Cath




Regards,
Cath

"Hang on lads, I've got a great idea. "
Michael Caine (Charlie Croker) The Italian Job
Post #594959
Posted Friday, October 31, 2008 7:40 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 2:01 PM
Points: 659, Visits: 6,104
I copied and pasted this into a deummy db and it all compiled fine. Are you using SQL 2005? I If not, you'll need to change varchar(max) to something like varchar(10) or some other value large enough to hold your results. made the assumption that since you posted in the 2005 are that you were. If you're using


DAB
Post #594967
Posted Friday, October 31, 2008 7:57 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 405, Visits: 1,136
Ok cool.

All the help is appreciated!

- thanks all.


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #594986
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse