How make the code replace in one line instead of 5

Question

I'm running a sql code to format the data from txt to decimal. the code I wrote work well but slow

remove . replace , with . flag empty rows as NULL convert NUll in 0

this looks to run 5 times instead of 1

UPDATE PL01_SHIPMENT SET [Shipment Net Profit By Chargeable Wight] = REPLACE([Shipment Net Profit By Chargeable Wight], '.', '')
UPDATE PL01_SHIPMENT SET [Shipment Net Profit By Chargeable Wight] = REPLACE([Shipment Net Profit By Chargeable Wight], ',', '.')
UPDATE PL01_SHIPMENT SET [Shipment Net Profit By Chargeable Wight] = REPLACE([Shipment Net Profit By Chargeable Wight], 'zl', '')
UPDATE PL01_SHIPMENT SET [Shipment Net Profit By Chargeable Wight] = NULLIF([Shipment Net Profit By Chargeable Wight], '')
UPDATE PL01_SHIPMENT SET [Shipment Net Profit By Chargeable Wight] = ISNULL([Shipment Net Profit By Chargeable Wight],0)
ALTER Table dbo.PL01_SHIPMENT
ALTER COLUMN [Shipment Net Profit By Chargeable Wight] DECIMAL(18,2)  NULL
GO

it is possible to write some steps in 1 line so is not querying 5 time?

this is how the data are stored enter image description here

some rows are empty that why I was thinking about NULL

You can pass in the result of a function into another function. For example, Replace(Replace(col, '.', ''), ',', '.') and so forth. — Kei, Sep 17 '19 at 13:50
Not sure but [This might help](https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query) — champion-runner, Sep 17 '19 at 13:54
@Kei could you help me with the syntax to do that for 5 replace? — matt, Sep 17 '19 at 13:56
@Kei I try the code below but still I get an error in conversion, no idea why — matt, Sep 17 '19 at 14:18
@matt, given that you are later altering your column to Decimal(18,2) I suspect that `[Shipment Net Profit By Chargeable Wight]` is the wrong column type. You shouldn't be constantly changing your column type on the fly. Instead, if you receive your data as text and need to convert it into a decimal, I suggest keeping two columns instead. For example, ShipmentNetProfitByChargeableWeightRaw and ShipmentNetProfitByChargeableWeightValue. Then you can do the replacements on ShipmentNetProfitByChargeableWeightRaw, apply a cast to decimal, and store the result in the decimal column — Kei, Sep 17 '19 at 14:35

Gordon Linoff · Answer 1 · 2019-09-17T15:41:07.990

1

You can do this in one update:

UPDATE PL01_SHIPMENT
     SET [Shipment Net Profit By Chargeable Wight] = COALESCE(NULLIF(REPLACE(REPLACE(REPLACE([Shipment Net Profit By Chargeable Wight], '.', ''), ',', '.'), 'zl', ''), ''), 0);

This does exactly what your code does.

To prevent errors, you should do explicit conversions with try_convert() rather than implicit ones via coalesce():

     SET [Shipment Net Profit By Chargeable Wight] = COALESCE(TRY_CONVERT(DECIMAL(18,2), REPLACE(REPLACE(REPLACE([Shipment Net Profit By Chargeable Wight], '.', ''), ',', '.'), 'zl', '')), 0);

edited Sep 17 '19 at 15:41

answered Sep 17 '19 at 13:58

Gordon Linoff

1,242,037
58
646
786

I try run the code but I go an error conversion" Conversion failed when converting the varchar value '160.06 ' to data type int." I need to convert in decimal 18,2 – matt Sep 17 '19 at 14:08
I got an error about number type, my goal is to have a decimal (18,2) – matt Sep 17 '19 at 14:19
@matt . . . You should be getting the same error in your code. This is presumably due to the implicit conversion caused by using `0` for `coalesce()` rather than `'0'`. – Gordon Linoff Sep 17 '19 at 15:41

score -1 · Answer 2 · answered Sep 17 '19 at 13:56

-1

How about if you add keyword nolock after the table name. Maybe, it will improve slightly performance. For me, I will generate a function and call it by a recursive way.

answered Sep 17 '19 at 13:56

NP90s

13
5

could please explain more in detail how to use no lock – matt Sep 17 '19 at 14:20

dmr · Answer 3 · 2019-09-17T14:08:09.170

-1

Or if its being done in PHP for example, you could create the queries in a loop and replace the values as so:

$from = ['.',',','zl',''];
$to = ['','.','',''];
$sql = array_replace($from, $to);

for($ii=0;$ii<count($from);$ii++) {
$sql .= "UPDATE PL01_SHIPMENT SET [Shipment Net Profit By Chargeable Wight] = REPLACE([Shipment Net Profit By Chargeable Wight], '{$from[$ii]}', '{$to[$ii]}');";
}

Now you can use the sql query and add your alters which can be executed.

edited Sep 17 '19 at 14:08

answered Sep 17 '19 at 14:00

dmr

9
2

This would still generate one sql statement per replacement, which is not what OP wants. OP wants to do the update with all the replacements in one update query. – Kei Sep 17 '19 at 14:14

How make the code replace in one line instead of 5

3 Answers3