Converting SQL varchar column values to $ format i.e. thousand separation

Question

I have a varchar(256) column AttributeVal with all different type of text values.

I need to find out all $ values like $5000, $2000 etc & add thousand separator to them (only to these values, but not to the other text values present in that column).

Thus the updated values should look like $5,000 & $2,000.

If I am using following query, then it will end up converting all values & I need to concatenate $ manually :(

replace(convert(varchar, convert(Money, AttributeVal), 1), '.00', '')

NB : I know that these kind of formatting should be taken care in the application end, but our customer is adamant to have these customization to be stored in DB only.

declare @t varchar(20)= '$2000' select +'$'+replace(convert(varchar,convert(Money, @t),1),'.00','') — mohan111, Nov 05 '14 at 07:37

score 0 · Answer 1 · edited May 23 '17 at 12:13

I don't think you can do a replace statement based on a regular expression like that exactly. See this stackoverflow post asking the same question.

You may want to reinforce to your client that formatted data should not be stored in a database. That money value should probably be stored in a DECIMAL(13, 4) or something similar instead of a VARCHAR field mixed with other data as well.

Your question is a great example of why you don't want to do this. It makes simple things very difficult.

score 0 · Answer 2 · answered Nov 05 '14 at 07:42

0

Try this

SELECT '$'+ PARSENAME( Convert(varchar,Convert(money,convert(Money, 100000)),1),2)

Output: $100,000

Hope this help!

answered Nov 05 '14 at 07:42

Giau Huynh

300
3
15

score 0 · Answer 3 · answered Nov 05 '14 at 07:44

try with this, this will take care of thousand separator :-)

 '$'+convert(varchar(50), CAST(amount as money), -1) amount

Sample

;with cte (amount)
as
(
    select 5000 union all   
    select 123254578.00 union all
    select 99966.00 union all
    select 0.00 union all
    select 6275.00 union all 
    select 18964.00 union all 
    select 1383.36 union all
    select 26622.36


)
select '$'+convert(varchar(50), CAST(amount as money), -1) amount
from cte

score 0 · Answer 4 · answered Nov 05 '14 at 08:39

Here is my take on the problem:

select coalesce(cast(try_convert(money, value) as varchar(50)), value) converted
from (
    values ('50')
        , ('5000')
        , ('3000.01')
        , ('text')
    ) samples(value)

and the output:

converted
--------------------------------------------------
50.00
5000.00
3000.01
text

(4 row(s) affected)

Converting SQL varchar column values to $ format i.e. thousand separation

4 Answers4