Reputation: 23
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
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