how to add commas as thousand separator for numbers stored as text

Question

Post reply

how to add commas as thousand separator for numbers stored as text

applebutton

Mr or Mrs. 500

Points: 503
More actions
July 20, 2011 at 1:27 pm

#254685

I have numbers as text without thousand separator. Now I need to add comma as a thousand separator to this text number. How can I do this?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323506 More actions · Answer 1

check out Cast and Convert in Books online:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

if your original datatype is money, then one of the overloaded parameters of the CONVERT function is to insert the commas like you describe.

--resulkts = 1,234,567.89

with MySampleData as

(

select convert(money,1234567.8910)As TheVal

)

select convert(varchar,TheVal,1) --1 means Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

from MySampleData

in order for you to use tthat function, you'll need to go thru the extra step of converting your text value to money, and then using the convert function back to varchar, but make sure you include the optional second parameter.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

applebutton Mr or Mrs. 500 Points: 503 More actions · Answer 2

Is this also supported in SQL Server 2005?

Lowell (7/20/2011)
check out Cast and Convert in Books online:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
if your original datatype is money, then one of the overloaded parameters of the CONVERT function is to insert the commas like you describe.
--resulkts = 1,234,567.89
with MySampleData as
(
select convert(money,1234567.8910)As TheVal
)
select convert(varchar,TheVal,1) --1 means Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
from MySampleData
in order for you to use tthat function, you'll need to go thru the extra step of converting your text value to money, and then using the convert function back to varchar, but make sure you include the optional second parameter.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

Yes. But, since you're in the "Newbie" forum, I have to tell you that ...

1) Formatted data should not be permanently stored in SQL Server, period.

2) SQL Server should usually not be used for formatting. Do it in the GUI or reporting too, instead.

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

toalcaraz Valued Member Points: 73 More actions · Answer 4

select CONVERT(varchar, CAST(valor_avaluo_catastral as money), 1) from dbo.solicitud_permutas

Only need convert your numeric field in money cast(numeric_atribute as money) ... then convert this result to varchar with CONVERT function with 1 like parameter ... Thanks