Zeldarck Zeldarck - 5 months ago 17
SQL Question

Oracle SQL, varchar2, like with number

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 would like the first query to match.

MT0 MT0
Answer

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}?$' ) );