1

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

2 Answers 2

1
LEFT(substring([Expected Charges],(PATINDEX('%[123456789]%',[Expected Charges])),9),LEN(substring([Expected Charges],(PATINDEX('%[123456789]%',[Expected Charges])),9))-2)+'.'+RIGHT(substring([Expected Charges],(PATINDEX('%[123456789]%',[Expected Charges])),9),2) as 'Expected Charges'
Sign up to request clarification or add additional context in comments.

Comments

0

Unless I am missing something, I think you could simply do

cast(cast(replace(col,'TOTAL CHARGES:','') as decimal(10,2))/100 as decimal (10,2))

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.