Oracle SQL, varchar2, like with number

Question

Given a table with the NO_TELEPHONE column of data type VARCHAR2(20 BYTE) which contain either 10- or 4-digit phone numbers. I have to ensure the last 4 digit of every number are unique (so 0000001111 and 1111 could not both exist).

I have tried this:

SELECT * from table where NO_TELEPHONE like '%1111';`

But it found 0 result, I really don't understand why.

After some tries, I got results with:

SELECT * from table where NO_TELEPHONE like '1111%';
SELECT * from table where NO_TELEPHONE like '______1111%';  (there is 6 '_' )

If I have 0000001111 in table and I want insert 0000, so program will do:

SELECT * from table where NO_TELEPHONE like '0000%';

and it will match with 0000001111, is not the behavior I wanted

Answer 1

If you only want to select the data you can use:

SELECT RTRIM( no_telephone, CHR(13)||CHR(10) ) AS no_telephone
FROM   table_name
WHERE  RTRIM( no_telephone, CHR(13)||CHR(10) ) LIKE '%1111';

However, if you want to correct the data::

UPDATE table_name
SET    no_telephone = RTRIM( no_telephone, CHR(13)||CHR(10) )
WHERE  SUBSTR( no_telephone, -2 ) = CHR(13)||CHR(10);

Or, if there are more issues and you want to replace all non-digits then:

UPDATE table_name
SET  no_telephone = REGEXP_REPLACE( no_telephone, '\D+' );

Then you can enforce the uniqueness of the last 4 characters:

CREATE UNIQUE INDEX table_name__no_telephone__u
  ON table_name ( SUBSTR( no_telephone, -4 ) );

and you can enforce the format of the column using:

ALTER TABLE table_name ADD CONSTRAINT table_name__no_telephone__chk
  CHECK ( REGEXP_LIKE(no_telephone, '^\d{4}\d{6}?$' ) );