title: Stripping out non-digits - SQL0171 Argument of function TRANSLATE not valid
body: <p>I'm trying to extract the first ten digit numbers of a phone number, ending up with a 10-digit (or less) number.
  I need to use whitelisting, not blacklisting, due to special characters having been used. e.g. "á(123) 555-4567 Toll Free:á(891) 0" must become 1235554567.</p>
  
  <p>I'm trying to use <a href="https://stackoverflow.com/a/37685384">https://stackoverflow.com/a/37685384</a>.</p>
  
  <p>However, when I try to use this:</p>
  
  <pre><code>TRANSLATE(SFCONTACT.PHONE,'',TRANSLATE(SFCONTACT.PHONE,'','1234567890',''),'') as clean
  </code></pre>
  
  <p>I get</p>
  
  <blockquote>
    <p>Message: [SQL0171] Argument 04 of function TRANSLATE not valid.</p>
  </blockquote>
  
  <p>One of the comments said that using spaces instead of empty strings removed that. However, for me, trying:</p>
  
  <pre><code>TRANSLATE(SFCONTACT.PHONE,' ',TRANSLATE(SFCONTACT.PHONE,' ','1234567890',' '),' ') as clean
  </code></pre>
  
  <p>gives:</p>
  
  <blockquote>
    <p>Message: [SQL0171] Argument 03 of function TRANSLATE not valid.</p>
  </blockquote>
  
  <p>How can I accomplish this?</p>
  
  <p>Running an AS400 DB2, IBM version V7R1M0</p>
  
  <p><strong>EDIT</strong> (Not sure if this should be a separate Question or not)</p>
  
  <p>I tried this as suggested:</p>
  
  <pre><code>SUBSTR(REGEXP_REPLACE(PHONE, '[\D]', ''),1,10) AS MAINPHONE,
  </code></pre>
  
  <p>And at first it seemed to work; I was able to create a view, BBICNTMIG</p>
  
  <p>However, when I try to insert into a table using that view:</p>
  
  <pre><code>INSERT INTO AMMLIBC.BBICONTACT
  (COMPANY,CUSNO,SHIPTO,HONORIFICFK,FIRSTNAME,LASTNAME,EMAIL,MAINPHONE,TYPEFK,PROSPECTFK,CREATEDBY,CREATEDAT)
  SELECT COMPANY,CUSNO,SHIPTO,HONORIFICFK,FIRSTNAME,LASTNAME,EMAIL,MAINPHONE,TYPEFK,PROSPECTFK,CREATEDBY,CREATEDAT
  FROM AMMLIBC.BBICNTMIG
  </code></pre>
  
  <p>it gives:</p>
  
  <blockquote>
    <p>Message: [SQL0420] Character in CAST argument not valid. Cause . . . . . :   A character in the argument for the CAST function was not correct. Recovery  . . . :   Change the result data type to one that recognizes the characters in the CAST argument, or change the argument to contain a valid representation of a value for the result data type. Try the request again.</p>
  </blockquote>
  
  <p>If I remove the phone numbers from the insert (taking default value of null instead), then the INSERT succeeds, so I know it's the phone number causing this.</p>
  
  <p>The column type in the destination table is NUMERIC(10,0). I tried using this, but no change:</p>
  
  <pre><code>CAST(SUBSTR(REGEXP_REPLACE(PHONE, '[\D]', ''),1,10) AS NUMERIC(10,0)) AS MAINPHONE,
  </code></pre>
  
  <p>Further info:</p>
  
  <p>I tried casting to char before casting to numeric. No change.</p>
  
  <p>I tried adding a where clause (both "mainphone is null" and "mainphone is not null" do this) and the error message changes to:</p>
  
  <blockquote>
    <p>Message: [SQL0802] Data conversion or data mapping error. Cause . . . . . :   Error type 6 has occurred. [...] 6 -- Numeric data that is not valid.</p>
  </blockquote>