I need to extract numeric values from a string value and then convert that value to a decimal type. I need to subtract another value from this field. My query is:
SELECT TOP (1000)
substring([Expected Charges],(PATINDEX('%[123456789]%',[Expected Charges])),9) as 'Expected Charges',
[Expected Charges] as 'Expected Charges 2'
FROM [AX_2C_Prod_DW].[dbo].[AAATRANSPORTTABLE_V]
My output is:
Expected Charges Expected Charges 2
9991 TOTAL CHARGES: 000000009991
9991 TOTAL CHARGES: 000000009991
I need this output:
Expected Charges Expected Charges 2
99.91 TOTAL CHARGES: 000000009991
99.91 TOTAL CHARGES: 000000009991
I tried to add the convert function to my query:
SELECT TOP (1000)
convert(decimal(18,2),substring([Expected Charges],(PATINDEX('%[123456789]%',[Expected Charges])),9)) as 'Expected Charges',
[Expected Charges] as 'Expected Charges 2'
FROM [AX_2C_Prod_DW].[dbo].[AAATRANSPORTTABLE_V]
but it produced these results:
Expected Charges Expected Charges 2
9991.00 TOTAL CHARGES: 000000009991
9991.00 TOTAL CHARGES: 000000009991