I'm working with an old program that has all of the queries to the database written with each VB.NET page. I will be re-writing this program using the latest development tools but I am currently being asked to modify data formats by adding commas, changing to decimal, formatting date...etc.
Can you point me to resouces on writing SQL statements that will reformat data?
Answer by Steve Jones - Editor ·
T-SQL isn't the best language fro formatting strings, or even dates/numbers. Most front end languages have much better functions for formatting things. However, as Grant has mentioned, CAST/CONVERT and SUBSTRING, CHARINDEX, REPLACE will help.
Answer by Ian Roke ·
You really should be using the application to format the data after it has been transferred from the database.
It is possible to use examples such as
REPLACE(CONVERT(varchar, CONVERT(money, [Value]), 1), '.00', '') to format a number with no decimal places with comma separators but you can see it is calling three functions each line.
It is much better to send the number to your application and then format it there.
Answer by Grant Fritchey ·
Take a look in Books Online and look up the functions for CAST and CONVERT, at least to start. You may also need to mess around with the string manipulation functions so you'll want to look up things like STUFF, REPLACE, CHARINDEX... just to start. But it really depends on what you want to do in detail.
Answer by Jeff Moden ·
The reason why it's better to do this type of formatting in the GUI is because the GUI code can take advantage of regional settings automatically. That includes warping dates to several different European formats and using periods instead of commas in numbers where appropriate, etc.
There's also some performance reasons for doing it in the GUI. First, formatting is a relatively expensive thing to do and distributing that expense to the machine the end user has instead of concentating it all in the same server has some serious merit. Second, if you add things like currency symbols, commas, etc, you actually have more bytes to send "through the pipe" and every byte counts on a busy system.
Last, but not least, if you format on the server side and requirements change where some calculations need to occur on the GUI side, then you have to undo the formatting, do the calculation, and then redo the formatting.
Unless you don't have a GUI involved or must write to a file to meet customer requirements, you should never format data on the DB Server side.