Multiple update replaces to get the code looking correct

Question

This is my slightly grotty code that works.

update P_INVENTORY_MOVEMENTS set qty = REPLACE(qty,' ','')
update P_INVENTORY_MOVEMENTS set qty = REPLACE(qty,'^','')
UPDATE P_INVENTORY_MOVEMENTS SET qty = replace(replace(qty,CHAR(13),''),CHAR(10),'')
UPDATE P_Inventory_movements SET QTY = '0.00' where isnumeric (qty)<>1

It removes whitespace, then hats, then newlines and carriage returns, and then finally for any values left, sets it to 0.00

I think this may be quite poorly written, and I wondered if anyone can see a better way of doing it. Would the best way be to use a larger nested replace or am I approaching this wrong?

If you are using sql server 2012 you should be using [`Try_Cast()` - msdn](https://msdn.microsoft.com/en-us/library/hh974669.aspx) — SqlZim, Jan 15 '17 at 15:01

Pரதீப் · Accepted Answer · 2017-01-15T16:15:33.347

4

Try this way

;WITH cte
     AS (SELECT Replace(Replace(Replace(Replace(qty, ' ', ''), '^', ''), Char(13), ''), Char(10), '') new_qty,
                qty
         FROM   P_INVENTORY_MOVEMENTS
         WHERE  qty LIKE '% %'
                 OR qty LIKE '%^%'
                 OR qty LIKE '%' + Char(13) + '%'
                 OR qty LIKE '%' + Char(10) + '%'
                 OR qty LIKE '%[^0-9.]%')
UPDATE cte
SET    QTY = CASE
               WHEN new_qty LIKE '%[^0-9.]%' THEN '0.00' else new_qty
             END

Better to alter the datatype of QTY to Numeric to avoid inserting bad data in future

edited Jan 15 '17 at 16:15

answered Jan 15 '17 at 14:22

Pரதீப்

91,748
19
131
172

should the where clause be `qty like ... ` instead? – Vamsi Prabhala Jan 15 '17 at 14:25
The original updates don't turn strings with invalid characters to `''`, it just removes those invalid characters. – SqlZim Jan 15 '17 at 14:31
This won't handle cases where qty has more than one of: spaces, hats, /r /n) – SqlZim Jan 15 '17 at 14:36
@Prdp Yes sorry to be difficult but it seems to only be able to do one of the changes, not all of them, for example if I have /r or /n it won't remove hat as well – Jimmy Jan 15 '17 at 14:39
`like '%[^0-9]%'` matches '.', so 0.00 matches `like '%[^0-9]%'`, add '.' to the like as `like '%[^0-9.]%'` – SqlZim Jan 15 '17 at 14:40
@SqlZim - updated.. not sure about your last comment – Pரதீப் Jan 15 '17 at 14:46
@Pdrp I'm saying your like needs to include a period as a valid character. `'%[^0-9.]%' ` – SqlZim Jan 15 '17 at 15:03
@Pdrp if qty = '1.0 0' your update would turn it to '0.00' http://rextester.com/USX3844 – SqlZim Jan 15 '17 at 15:11
I would also allow for negative values '%[^0-9.-]%' – SqlZim Jan 15 '17 at 16:04
Shouldn't your case include `else new_qty` before `end`? -- otherwise turning the good values into null. – SqlZim Jan 15 '17 at 16:14
@Prdp `OR qty LIKE '%[^0-9.]%'` covers all of the other cases, you can reduce your `where` down to `where qty like '%[^0-9.]%'` http://rextester.com/TYNT6142 -- and I would still change it to include negative values by using `where qty like '%[^0-9.-]%'` – SqlZim Jan 15 '17 at 16:23

score 2 · Answer 2 · answered Jan 15 '17 at 14:50

Compute replacement in CROSS APPLY and update with CASE

   UPDATE P_Inventory_movements 
   CROSS APPLY (SELECT qtyr = REPLACE(REPLACE(REPLACE(REPLACE(qty
                        ,' ',''),CHAR(13),''),CHAR(10),''),'^','') 
                 ) x
    SET QTY = CASE isnumeric(qtyr) WHEN 1 THEN qtyr ELSE '0.00' END

score 1 · Answer 3 · answered Jan 15 '17 at 14:44

1

You could nest the replace statements, and use just 2 update statements instead of 4:

UPDATE P_Inventory_movements 
SET QTY =   REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(qty,' ','')
                    ,CHAR(13),'')
                ,CHAR(10),'')
            ,'^','')

UPDATE P_Inventory_movements 
SET QTY = '0.00' 
WHERE isnumeric (qty)<>1

answered Jan 15 '17 at 14:44

Zohar Peled

79,642
10
69
121

Yeah, well, the second update statement is only a well formatted version of the update statement in the question, I didn't change it – Zohar Peled Jan 15 '17 at 15:00

score 1 · Answer 4 · answered Jan 15 '17 at 16:02

If:

this is sql server 2012+ for try_cast() and try_parse()
I don't have access to CLR (e.g. the regex in SQL#)
I don't want to use a UDF to strip out more unwanted characters
the destination and usage would be decimal(9,2)
I am really sure this value should be qty despite any formatting issues

Then I would use this to try and deal with dirty data:

update p_inventory_movements set 
  qty = replace(replace(replace(replace(
           qty
          ,char(13),'')
          ,char(10),'')
          ,' ','')
          ,'^','')
  where qty like '%[^0-9.-]%';

update p_inventory_movements set 
  qty = coalesce(
     try_parse(updqty as decimal(9,2))
    ,try_parse(updqty as float)
    ,try_parse(updqty as money))
    ,'0.00'
    )
  where try_cast(qty as decimal(9,2)) is null;

Testing various bad input with different functions: http://rextester.com/KHNE65079

Multiple update replaces to get the code looking correct

4 Answers4

If: