Need HELP on data cleaning query - simple really, just forgot syntax

• randyv

  SSC Veteran

  Points: 215

  More actions

  January 30, 2004 at 6:38 am

  #162997

  Hello all -

  Well, I'm stranded here without my books, and I have a brain cramp and cannot access my

  memory.

  Here's the problem -

  I'm loading varchar info into a staging table.

  columns are qtyavail and qtyatbranchavail.  I want to add these and set a 3rd column to that value.  Whilst trying to do this with cast, I discovered a data integrity problem in that some yutz has been entering commas into the qty fields.  Instead of 1024.00, I have 1,024.00 (example).

  I used to have an SQL query that could parse through strings and cleanse this kind of stuff (but it was on another database)  I'd use Substring and Length and I think the other was Index where, on this database, index would return a character position.  So, I could use length to get each row's length for the column, parse through the substring and find the numeric position of the offending character(s) and then take action.

  Does anyone have any ideas on how I could do this?  Thanks.

  randyvol

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  January 30, 2004 at 7:03 am

  #492315

  CAST(REPLACE(qtyavail,',','') as numeric(9,2)) + CAST(REPLACE(qtyavail and qtyatbranchavail) as numeric(9,2))

  Far away is close at hand in the images of elsewhere.
  Anon.

• randyv

  SSC Veteran

  Points: 215

  More actions

  January 30, 2004 at 7:44 am

  #492325

  THANKS !!!!