Formating numbers

Question

Post reply

Formating numbers

amigoface

SSC Eights!

Points: 991
More actions
November 5, 2011 at 1:54 pm

#247462

hi,
I am trying to migrate a database from firebird to sqlserver
So I translate stored procedures as they are written
some stored procedure contain one UDF function that take a number and a format and make the formatting
the format can be variable ; for example :
#0.00, ### ##0.00 .... and things like that
after some searching i did'nt found a solution to this problem
any help is welcome
thanks and good day

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What exactly is your question?

In general, formatting should be done at the presentation layer.

If it needs to be done using T-SQL, there are several options.

Which one could be used in your scenario, depends on the requirement.

Please elaborate and add some examples.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

amigoface SSC Eights! Points: 991 More actions · Answer 2

well ,

the context : i have to migrate a database appliation from firebird to SQL server

the original database contain a stored procedure that use a formating function that doesn't exist in sql server

this formating "UDF" function take a number and a format as parameter

i am searching for this function equivalent is sql server

or if i can use this dll "function" in sql server would be perfect

make sense ?

LutzM SSC Guru Points: 107049 More actions · Answer 3

A rephrase of the original question without providing any additional information doesn't really help any further.

And no, it still doesn't make sense to me.

However, even if there might be no built-in function in SQL Server, you might write one that meet your requirements.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

amigoface (11/5/2011)
well ,
the context : i have to migrate a database appliation from firebird to SQL server
the original database contain a stored procedure that use a formating function that doesn't exist in sql server
this formating "UDF" function take a number and a format as parameter
i am searching for this function equivalent is sql server
or if i can use this dll "function" in sql server would be perfect
make sense ?

That's what we're trying to find out... why do you need to do the formatting in SQL Server? I understand that the old Firebird proc would format the data for you but, unless it's for display purposes, it's just not necessary to format anything in SQL Server.

So... is your proc for display in a GUI or Reporting System? The recommendation and basic "best practice" is to let the GUI or Reporting System do the formatting at display time. If it's formatting the data to store it in a table, then that's probably the worst thing you could do to the database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

amigoface SSC Eights! Points: 991 More actions · Answer 5

well it's a little complicated

the formated result is not just for display, it's used by another stored procedure :w00t:

it's a relatively big medical application that i am in charge to port to the web using sql server

i am not the original developper of the app, so to be honest i am just translating database from firebird to sql server

and you are right , formatting should be done iin the client side

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Thanks for taking the time to provide the feedback. Based on what you said, unless rounding is somehow important to that other stored procedure, I wouldn't bother formatting the data that gets used by that other stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

amigoface SSC Eights! Points: 991 More actions · Answer 7

amigoface

SSC Eights!

Points: 991

November 7, 2011 at 6:39 am

#1403915

thanks for your time

really apreciate

ryvlad SSC Enthusiast Points: 162 More actions · Answer 8

hi, if it would not be a big problem can you post the

UTF from firebird here,

I would really like to see what it actually does...

as it was said before, formating the number to use in calculations, make no sense, you might as well perform proper value processing

as in (rounding, type conversion etc.) as needed.

if you do formating to be sued in GUI, ideally it should be the GUI responsibility to format output as needed rather then SQL.

BUT some times Lazy devs(present company excluded :hehe:)

do not conform to this standard.

I am guilty of this myself from time to time 🙂

amigoface SSC Eights! Points: 991 More actions · Answer 9

what do you mean by utf ?

if you mean tables and stored procedure code, i really doubt you can understand something ... i didn't 😛

it's a weird undocumented database wth crazy column names, and to be honest i don't have time and patience to understand what the original developper was trying to do 😉

since the database work well with it's desktop application , my easiest option is to translate the database to sql server and put it on a web host and ... basta !

finally i think that i will try to write a function in sql server that make the formatting

i hope you can understand

ryvlad SSC Enthusiast Points: 162 More actions · Answer 10

sorry a typo, I ment UDF

the code for the function that does formating

as far as I understand that should be more or less normal SQL code (right?)

just want to see what and how it does things under the hood.

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 11

The convert functions in T-SQL has more options for dates, but not for numbers.

The formatting of numbers to percent, currency, float, decimals etc. is usually done in the GUI of the application.

Could you paste the function you have in Firebird? I imagine the function in Firebird takes a number and format then return a text with the formatted number.

Regards,

Iulian

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 12

Your best bet is probably a CLI function based on .Net's Format function, assuming the format specifications in your application make sense in .Net.

You may have to add a layer to translate your app's format specs to .Net, but that's still going to be easier than writing it in T-SQL.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 13

If you can afford to wait 5 or 6 months, SQL Server 2012 is going to be released with what seems to be a great addition to the T-SQL commands: the FORMAT function. 😀

http://blog.sqlauthority.com/2011/09/17/sql-server-denali-string-function-format-a-quick-introduction/[/url]

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

amigoface SSC Eights! Points: 991 More actions · Answer 14

the udf is inside a dll and probably written in c++ or delphi ... lol