Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Finally with help of my colleagues I came to feasible solution. It has some limitations (sign is not considered, decimal part is not considered), but for IDs it works just fine. </p> <ol> <li>Trim spaces from both beginning and end of string</li> <li>Trim leading zeros from string</li> <li>Test for maximum allowed length</li> <li>Pad string with zeros to four chars (add four zeros to beginning of string and get last four characters from string)</li> <li>Test each position in string on set of allowed chars</li> </ol> <p>So the records from <code>SRC_TABLE</code> which cannot be converted to <code>DECIMAL(4)</code> can be obtained by select:</p> <pre class="lang-sql prettyprint-override"><code>select Some_Id from SRC_TABLE where characters(trim(leading '0' from trim(both ' ' from Some_Id))) &gt; 4 or substring(substring('0000' || trim(leading '0' from trim(both ' ' from Some_Id)) FROM characters('0000' || trim(leading '0' from trim(both ' ' from Some_Id))) - 3) FROM 1 FOR 1) NOT IN ('0','1','2','3','4','5','6','7','8','9') or substring(substring('0000' || trim(leading '0' from trim(both ' ' from Some_Id)) FROM characters('0000' || trim(leading '0' from trim(both ' ' from Some_Id))) - 3) FROM 2 FOR 1) NOT IN ('0','1','2','3','4','5','6','7','8','9') or substring(substring('0000' || trim(leading '0' from trim(both ' ' from Some_Id)) FROM characters('0000' || trim(leading '0' from trim(both ' ' from Some_Id))) - 3) FROM 3 FOR 1) NOT IN ('0','1','2','3','4','5','6','7','8','9') or substring(substring('0000' || trim(leading '0' from trim(both ' ' from Some_Id)) FROM characters('0000' || trim(leading '0' from trim(both ' ' from Some_Id))) - 3) FROM 4 FOR 1) NOT IN ('0','1','2','3','4','5','6','7','8','9'); </code></pre> <p>EDIT: More convenient is the way suggested by dnoeth in his answer to <a href="https://stackoverflow.com/questions/39386736/convert-char-to-int-teradata-sql">Convert char to int TeraData Sql</a> , which also works in TD 13.10:</p> <pre class="lang-sql prettyprint-override"><code>-- TO_NUMBER returns NULL when failing CAST(TO_NUMBER(UTENTE_CD) AS INTEGER) -- check if there are only digits CASE WHEN UTENTE_CD = '' -- all spaces THEN NULL WHEN LTRIM(UTENTE_CD, '0123456789') = '' -- only digits THEN CAST(UTENTE_CD AS INTEGER) ELSE NULL END </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSafe casting VARCHAR to DECIMAL in Teradata
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJohnyCash
    1. USJohnyCash
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSafe casting VARCHAR to DECIMAL in Teradata
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COWell done on coming up with a solution. Be aware though that while it works in your particular case, it is not equivalent to a proper numeric parser, since negative numbers and those which have a decimal point would be deemed invalid using this method.
      singulars
      1. PO
      1. USlins314159
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload