Anjana H Anjana H - 5 months ago 10
SQL Question

SQL function to change the column format

I have a staging table with column name LastAnalysisAmount (varchar data type) which contains the values

LastAnalysisAmount


0000000.00

0000114.77-

0000367.78

0290367.45

0090367.26-


This column holds both positive and negative numbers. I need to convert this LastAnalysisAmount column values into decimal with correct format and load this into destination table.
for example, in the destination table, the values should be


0.00

-114.77

367.78

290367.45

-90367.26


can you please help me to write a sql function to do this conversion?

Thank you for your help and time.

Answer

You can use REPLACE() and TRIM() with CASE EXPRESSION :

SELECT CASE WHEN LastAnalysisAmount LIKE '%-'
            THEN '-' + REPLACE(LTRIM(LastAnalysisAmount),'-','')
            ELSE LTRIM(LastAnalysisAmount)
        END as LastAnalysisAmount 
FROM YourTable

To load it as decimal to destination table:

INSERT INTO Destination_Table (LastAnalysisAmount)
SELECT CAST(CASE WHEN LastAnalysisAmount LIKE '%-'
                 THEN '-' + REPLACE(LTRIM(LastAnalysisAmount),'-','')
                 ELSE LTRIM(LastAnalysisAmount)
             END AS decimal(10,2))
FROM YourTable