SELECT DISTINCT incorrect name-CodePudding

I have table like this:

ID	FULL_NAME
1	Joe Char
2	Bob Maff
3	458
4	hi789
5	Kate Mill
6	33332263333226

and I want select only incorrect name, like this:

ID	FULL_NAME
3	458
4	hi789
6	33332263333226

I think about it that way and how correct is that?

select * from table 
where full_name like '%1%' 
   or full_name like '%2%' 
   or full_name like '%3%' 
   or full_name like '%4%' 
   or full_name like '%5%' 
   or full_name like '%6%'

CodePudding user response：

Your correct names have a space character in them so the incorrect ones do not have a space:

SELECT *
FROM   table
WHERE  full_name NOT LIKE '% %'

Or, it could be when they have a digit character:

SELECT *
FROM   table
WHERE  REGEXP_LIKE(full_name, '\d');

Or, without (slow) regular expressions:

SELECT *
FROM   table
WHERE  TRANSLATE(full_name, '0123456789', '----------' ) != full_name;

CodePudding user response：

Your example shows that "invalid names" are those that contain digits. If that's so, for sample data

SQL> select * from test;

        ID FULL_NAME
---------- --------------
         1 Joe Char
         2 Bob Maff
         3 458
         4 hi789
         5 Kate Mill
         6 33332263333226

6 rows selected.

query you might be using is

SQL> select *
  2  from test
  3  where regexp_instr(full_name, '\d') > 0;

        ID FULL_NAME
---------- --------------
         3 458
         4 hi789
         6 33332263333226

SQL>