I have one column in my SQL table where litres come in different style format. Column Name is litres.
UPDATE dbo.LostGear
SET Litres = REPLACE(Litres, 'ltrs', 'L')
WHERE Litres = 60ltrs
This is very time consuming as I have more than thousand records. Is there any SQL way in which 1000 entries can be formatted? I have mention all the different possibilities that exist in Litres column.
UPDATE #t
SET Litres =
RTRIM
(
CASE
WHEN RIGHT(Litres, 4) = 'ltrs'
THEN LEFT(Litres, LEN(Litres) - 4)
WHEN RIGHT(Litres, 3) = 'ltr'
THEN LEFT(Litres, LEN(Litres) - 3)
ELSE Litres
END
) + 'L'
WHERE RIGHT(Litres, 1) <> 'L';
Why don't you just store only the number and keep the formatting away and leave it to the application/end user? This way you can store only numbers and speed up the performance.