Varchar to numeric?

Other SQL Server Topics
1

Hello All,

I need to sql assistance with a field in my table. The field in the table is Pay_rate. It's a varchar(12) in the table. The problems is the result. The field looks like the below in the table.

PAY_RATE

15052000
14000040
244700
300000

I would like for it to look as such.

150,520.00
140,000.40
2,447.00
3,000.00

Any help is greatly appreciated. I tried the below, but it didn't work.

CAST(PPWG.PPWG_PAY_RATE AS NUMERIC(12,0)) AS RESULT

2

CAST(PPWG.PPWG_PAY_RATE AS NUMERIC(12,2))/100 AS RESULT

1 Like
3

Don't suppose you can get that changed? IMHO it is crazy to be storing a decimal value, like that, in a Text String column.

If you need the commas in your display (rather than just the decimals) you should ideally use formatting at the client end, but if that is not possible you could convert Money to String in order to get the comma thousand-separators. In this example I'm using an intermediate money variable just to show the working, but you could do the conversion from string to money, and money to formatted string in a single statement

DECLARE @PPWG_PAY_RATE	varchar(12) = '14000040',
	@tempMoney	money

SELECT	@tempMoney = CONVERT(money, @PPWG_PAY_RATE) / 100.0

SELECT	[@tempMoney] = @tempMoney

SELECT	[Varchar Convert] = CONVERT(varchar(20), @tempMoney, 1)

@tempMoney            
--------------------- 
140000.4000

Varchar Convert      
-------------------- 
140,000.40