Varchar To Number with Commas 1

[jw2000]

Due to poor design, we have a price field that is a varchar in the database. In a SQL stored procedure how can I add commas to this varchar and print out a varchar?


eg.
10000000 -> 10,000,000
5999 -> 5,999
2123112 -> 2,123,112
123 -> 123
332189 -> 332,189

 

[gmmastros]

faq183-4418

This faq's assumes your original number is a numeric(32,2), so you'll need to convert it from varchar to numeric before using the function.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Rhys666]

Try;

SELECT
	CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1)
FROM
	TABLE_NAME

Please note this deals with NULL values by replacing them with a value of '0'

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra
If life were fair, Dan Quayle would be making a living asking 'Do you want fries with that?' John Cleese

 

[jw2000]

750000 ->750,000.00
225000 -> 225,000.00

How can I do this with the decimal or the trailing zeros?

 

[jw2000]

I mean - How can I do this without the decimal or the trailing zeros?

I am assuming NULLs with still be replaced with a value of '0'

 

[gmmastros]

Rhys666's solution is better than mine. To remove the trailing zeros...

With trailing zeros.
SELECT
CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0))[red], 1[/red])
FROM
TABLE_NAME

Without trailing zeros.
SELECT
CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)))
FROM
TABLE_NAME

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jw2000]

gmmastros,

Your solution still has trailing zeros for CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0))) and it doesn't have commas which I need.

eg.
10000000 -> 10000000.00
5999 -> 5999.00
2123112 -> 2123112.00
123 -> 123.00
332189 -> 332189.00

 

[jw2000]

I need to figure out how to get the following:

10000000 -> 10,000,000
5999 -> 5,999
2123112 -> 2,123,112
123 -> 123
332189 -> 332,189

 

[Rhys666]

For Information;

Using CONVERT:
CONVERT
(
data_type ( length ),
expression ,
style
)

Arguments
Expression;
Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.

Data_Type;
Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.

Length;
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

Style;
Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

Money/SmallMoney Styles;
0 (default)
No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98

1
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

2
No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra

Church of the Flying Spaghetti Monster

 

[gmmastros]

I'm sorry. My brain hasn't kick started yet. I may be a couple cups of coffee low (in the caffeine department).

This should work for you. Since your output is a varchar, you can replace '.00' with nothing.

Select Replace(Convert(VarChar(100), Convert(Money, 750000, 0),1), '.00', '')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Rhys666]

It's horrible and may be better wrapped up in a UDF but;

SELECT
    LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1))-3)
FROM
	TABLE_NAME

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra

Church of the Flying Spaghetti Monster

 

[jw2000]

Rhys666,

If

LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1))-3)

returns 0, how can I replace the '0' with 'yes'


I tried

REPLACE(LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1))-3), '0', 'yes')

but

10000000 -> 1yes,yesyesyes,yesyesyes


How can I get 10000000 -> 10,000,000 and 0 to return 'yes'

 

[jw2000]

ie

10000000 -> 10,000,000
5999 -> 5,999
2123112 -> 2,123,112
123 -> 123
332189 -> 332,189
0 -> yes

 

[Rhys666]

I would probably put the conversion into a function so you're returning a formatted varchar value as you expect it, (I.E., 1,000,000 or 1,000 or 0). Then I'd likely wrap a case statement around that element of the select something like;

SELECT
    Budget = CASE dbo.FUNCTION_NAME(FIELD_NAME)
        WHEN dbo.FUNCTION_NAME(FIELD_NAME) = '0' THEN 'Yes'
        ELSE dbo.FUNCTION_NAME(FIELD_NAME)
    END,
FROM TABLE_NAME

or

SELECT
    Budget = CASE LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1))-3)
        WHEN LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1))-3) = '0' THEN 'Yes'
        ELSE LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(FIELD_NAME, 0)), 1))-3)
    END,
FROM TABLE_NAME

...but ain't that secone UGLY


NB: This is completely untested as I'm at now chilling home and don't have access to Sql Server

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra

Church of the Flying Spaghetti Monster

 

[jw2000]

Thank you Rys! Appreciate it.