Filtering records not containing numbers

I have a table that has numbers in string format. Ideally the table should contain 10 digit number in string format, but it has many junk values. I wanted to filter out the records that are not ideal in nature.

Below is the sample table that I have:

+---------------+--------+----------------------------------+
|    ID_UID     | Length |            ##Comment             |
+---------------+--------+----------------------------------+
| +112323456705 |     13 | Contains special character       |
|  4323456432   |     11 | Contains blank                   |
| 3423122334    |     10 | As expected, 10 character number |
| 6758439239    |     10 | As expected, 10 character number |
| 58_4323129    |     10 | Contains special character       |
| 4567$%6790    |     10 | Contains special character       |
| 45684938901   |     11 | Is 11 characters                 |
| 4568 38901    |     10 | Contains blank                   |
+---------------+--------+----------------------------------+

Expected Output:

+---------------+--------+----------------------------+
|    ID_UID     | Length |         ##Comment          |
+---------------+--------+----------------------------+
| +112323456705 |     13 | Contains special character |
|  4323456432   |     11 | Contains blank             |
| 58_4323129    |     10 | Contains special character |
| 4567$%6790    |     10 | Contains special character |
| 45684938901   |     11 | Is 11 characters           |
| 4568 38901    |     10 | Contains blank             |
+---------------+--------+----------------------------+

Basically I want all the records that dont have 10 digit numbers in them.

I have tried out below query:

SELECT *
FROM t1
WHERE ID_UID LIKE '%[^0-9]%'

But this does not returns any records.

Have created a fiddle for the same.

P.S. The columns length and ##Comment are illustrative in nature.

Answer

You want RLIKE not LIKE:

SELECT *
FROM t1
WHERE ID_UID RLIKE '[^0-9]'

Note that % is a LIKE wildcard, not a regular expression wildcard. Also, regular expressions match the pattern anywhere it occurs, so no wildcards are needed for the beginning and end of the string.

If you want to find values that are not ten digits, then be explicit:

SELECT *
FROM t1
WHERE ID_UID NOT RLIKE '^[0-9]{10}$'

Advertisement

Answer