Avoiding Repetitive Conditions In The Select Case And Where Clause

I have a table say TAB1 with the following columns - USER_ID NUMBER(5), PHN_NO1 CHAR(20), PHN_NO2 CHAR(20) I have to fetch records from TAB1 into another table TAB2 such that all r

Solution 1:

Sure you can! You do have to use some conditions though:

INSERTINTO New_Phone 
        SELECT user_id, phn_no1, phn_no2
        FROM (SELECT user_id,
              CASEWHEN LENGTH(TRIM(phn_no1)) =10ANDTRIM(phn_no1) like'5%'THEN SUBSTR(phn_no1, 1, 10) ELSENULLEND phn_no1, 
              CASEWHEN LENGTH(TRIM(phn_no2)) =10ANDTRIM(phn_no2) like'5%'THEN SUBSTR(phn_no2, 1, 10) ELSENULLEND phn_no2
              FROM Old_Phone) OldWHERE phn_no1 ISNOTNULLOR phn_no2 ISNOTNULL;

(I have a working SQL Fiddle example.)

This should work on any RDBMS. Note that, because of your data, this isn't likely to be less performant than your original (which would not have used an index, given the TRIM()). It's also not likely to be better, given that most major RDBMSs are able to re-use the results of deterministic functions per-row.

Oh, it should be noted that, internationally, phone numbers can be up to 15 digits in length (with a minimum in-country of 6 or less). Maybe use VARCHAR (and save yourself some TRIM()s, too)? And INTEGER (or BIGINT, maybe TINYINT) is more often used for surrogate ids, NUMBER is a little strange.