benz bola
benz bola

Reputation: 23

SQL Server : need add a dot before two last character

I have a data extracted from a flat file, and I need to add a dot before two last character.

For example

Original table:

Price   Discount      Coupon 
-----------------------------
30000    3000         5060  
12500    2050            0  
 5900     590            0  
59000    5900         1000  
 7030       0            0  
37100    5020            0  
21800       0         5060  
 3000    1020            0  

Transformed table:

Price    Discount      Coupon 
-------------------------------
300.00     30.00       50.60  
125.00     20.50        0  
 59.00      5.90        0  
590.00     59.00       10.00  
 70.30      0           0  
371.00     50.20        0  
218.00      0          50.60  
 30.00     10.20        0  

I tried with the following query in SQL Server:

SELECT 
    (LEFT([Price], LEN([Price]) - 2)) + '.' + RIGHT(RTRIM([price]), 2) 
FROM 
    dbo.Rawdata

But it only works with the first column

Msg 537, Level 16, State 2, Line 4
Invalid length parameter passed to the LEFT or SUBSTRING function.

Thanks so much. . .

Upvotes: 0

Views: 1137

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

try add a check for 0

SELECT case when Coupon <> '0' 
     then  (LEFT([Price],LEN([Price] )-2) ) +'.'+RIGHT(RTRIM([price]),2 )  
     else 0 end 
    FROM dbo.Rawdata

or

SELECT (LEFT([Price],LEN([Price] )-2) )+'.'+RIGHT(RTRIM([price]),2 ) 
FROM dbo.Rawdata
where Coupon <> '0' 

Upvotes: 2

Related Questions