Replace SQL old with new values

Transact-SQL
1

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.

Desired Format: NumberL( e.g,20L,40L,...)
Litres

1L
20
40
20
60ltrs
40 ltrs
80
2
20L
60L
60
22
1L
20ltr
40ltr
20ltr
60ltr
20ltrs
20 ltrs
20ltrs
20 ltrs
2
80
20ltrs
20 ltrs

2

You Litres column in LostGear should really be a decimal or an integer.

If you really have to cope with a string please post consumable test data in future:

CREATE TABLE #t
(
	Litres varchar(20)
);
INSERT INTO #t
VALUES ('1L'),('20'),('40'),('20'),('60ltrs'),('40 ltrs'),('80'),('2'),('20L')
	,('60L'),('60'),('22'),('1L'),('20ltr'),('40ltr'),('20ltr'),('60ltr')
	,('20ltrs'),('20 ltrs'),('20ltrs'),('20 ltrs'),('2'),('80'),('20ltrs')
	,('20 ltrs');

Try the following:

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';
1 Like
3 
UPDATE dbo.LostGear
SET Litres = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    Litres, 'e', ''), 'i', ''), 'l', ''), 'r', ''), 's', ''), 't', ''))) + 'L'
1 Like
4

So now you've created the next problem :slight_smile:

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.

1 Like