How to find a row where col have alphabets,numbers or special characters (except hyphen,apostrophe and space) in Oracle SQL

Question

I have to find the name like : Robert@jr23 (There must be Alphabet,Any Special characters or Numbers except hyphen(-),apostrophe (') and Space).

I was doing as below:

select * from test where REGEXP_LIKE(trim(NAME_1), '[^- '']')

But I am not getting the right results with this.

Need to match:

Kevin#123
bob@jr
mike$dr

Needs to exclude:

Alex-jr
Robert'jr
Brian jr

Please provide more examples of names that match and that don't match. What is `NAME_1`? — Gordon Linoff, Jul 17 '20 at 12:42
I think you need `not regex_like`. Otherwise you are matching strings that contain a valid character, which is all of them. — William Robertson, Jul 17 '20 at 12:54

William Robertson · Answer 1 · 2020-07-18T08:50:16.330

1

You need to use not with a pattern matching the values you want to exclude. Otherwise you are matching strings that contain any character that is not in the exclusion list, which is all of them.

select column_value
from   ora_mining_varchar2_nt
       ( 'Kevin#123'
       , 'bob@jr'
       , 'mike$dr'
       , 'Alex-jr'
       , 'Robert''jr'
       , 'Brian jr'
       , 'A!' )
where  1=1
and    not regexp_like(column_value,'[- '']')
and    regexp_like
       (column_value, '([A-Z0-9]+[^A-Z0-9])|([^A-Z0-9]+[A-Z0-9])', 'i') ;

Edit: added a regex_like condition to ensure that values contain a letter or digit and a 'special character', meaning here a character that is neither a letter, digit or space, ' or -.

edited Jul 18 '20 at 08:50

answered Jul 17 '20 at 13:12

William Robertson

15,273
4
38
44

that works but I only get the records where space, apostrophe and Hyphen is not present but how do I get where I have other special characters. – Kaushal Talniya Jul 17 '20 at 13:21
What other special characters? This matches all the examples you gave. Is the rule that it must also contain some special characters? – William Robertson Jul 17 '20 at 13:22
Yes The name must contain some special characters except space, Hyphen and apostrophe. – Kaushal Talniya Jul 17 '20 at 13:26
You can add another `regex_like` clause with those characters. If you specify your requirement with examples I can add it in. – William Robertson Jul 17 '20 at 13:27
What if no specific special characters are defined to take. Basically I need to select a customer name where there must be a special character or numbers (except space, Hyphen and apostrophe) – Kaushal Talniya Jul 17 '20 at 13:31
Does 'Special Characters' mean 'any printable character except letters, numbers, `-`, `'` and space'? – William Robertson Jul 17 '20 at 18:19
Yes Special Characters means any printable character except letters,numbers -, ' and space'..... But if I use the regex_like condition I didn't get any records. – Kaushal Talniya Jul 20 '20 at 09:09

Littlefoot · Answer 2 · 2020-07-20T12:03:56.250

One option is to replace everything that's valid with NULL, and what remains is invalid:

SQL> with test (col) as
  2    (select 'Robert@jr23' from dual union all
  3     select 'Kevin#123'   from dual union all
  4     select 'bob@jr'      from dual union all
  5     select 'mike$dr'     from dual union all
  6     select 'Alex-jr'     from dual union all
  7     select 'Robert''jr'  from dual union all
  8     select 'Brian jr'    from dual
  9    )
 10  select col
 11  From test
 12  where regexp_replace(col, '[[:alpha:]]|[[:digit:]]|-|''', null) is not null;

COL
-----------
Robert@jr23
Kevin#123
bob@jr
mike$dr
Brian jr

SQL>

I'm confused about what you, actually, want to get as a result. Here are some examples which show result of such a regular expression; which IDs do you want to get as a result?

SQL> with test (id, col) as
  2    (select 1, 'Robert@jr23' from dual union all
  3     select 2, 'Kevin#123'   from dual union all
  4     select 3, 'bob@jr'      from dual union all
  5     select 4, 'mike$dr'     from dual union all
  6     select 5, 'Alex-jr'     from dual union all
  7     select 6, 'Robert''jr'  from dual union all
  8     select 7, 'Brian jr'    from dual union all
  9     select 8, 'Brian10'     from dual
 10    )
 11  select col,
 12  regexp_replace(col,
 13    '[[:alpha:]]|[[:digit:]]|[[:space:]]|-|''', null) result
 14  from test;

COL         RESULT
----------- ----------
Robert@jr23 @
Kevin#123   #
bob@jr      @
mike$dr     $
Alex-jr
Robert'jr
Brian jr
Brian10

8 rows selected.

SQL>

One of your comments says:

select a customer name where there must be a special character or numbers (except space, Hyphen and apostrophe)

which means that numbers and special characters should be treated as "equal". If that's so, does this help?

SQL> with test (id, col) as
  2    (select 1, 'Robert@jr23' from dual union all
  3     select 2, 'Kevin#123'   from dual union all
  4     select 3, 'bob@jr'      from dual union all
  5     select 4, 'mike$dr'     from dual union all
  6     select 5, 'Alex-jr'     from dual union all
  7     select 6, 'Robert''jr'  from dual union all
  8     select 7, 'Brian jr'    from dual union all
  9     select 8, 'Brian10'     from dual
 10    )
 11  select id, col
 12  from test
 13  where regexp_replace(col, '[[:alpha:]]|[[:space:]]|-|''', '')
 14    is not null;

        ID COL
---------- -----------
         1 Robert@jr23
         2 Kevin#123
         3 bob@jr
         4 mike$dr
         8 Brian10

SQL>

Its not working for me. I need to select a name where I must have alphabets and special characters or numbers (except Hyphen, Space and Apostrophe) — Kaushal Talniya, Jul 20 '20 at 10:03
You mean, opposite of what that query returns? Try `where NOT regexp_replace ...` — Littlefoot, Jul 20 '20 at 10:15
Can you explain me how "regexp_replace(col, '[[:alpha:]]|[[:digit:]]|-|''', null) is not null" is working? — Kaushal Talniya, Jul 20 '20 at 10:38
It replaces letters or digits or hyphens or apostrophes with an empty string, which means that - if COL has anything but mentioned *characters* - those "anything but" characters are the result. So, if such a string is NOT NULL, it means that COL really has something but *letters/digits/hyphens/apostrophes*. — Littlefoot, Jul 20 '20 at 10:44
Okay, but your statement is fetching space and ignoring numbers , suppose I have 'Brian10', I should select this and If I have 'Brian Jr', I should not select it. — Kaushal Talniya, Jul 20 '20 at 10:50
True - I forgot about space. But, I'm confused now and can't tell what you want to get as a result. I edited my answer and added an example (IDs included) which shows what such a regular expression returns. Which IDs do you want to get as result? Could you name them all, please? For example, "I want to get IDs 1, 5 and 6". — Littlefoot, Jul 20 '20 at 11:07
Hopefully, I managed to understand the requirement and added another example. Have a look, please. — Littlefoot, Jul 20 '20 at 12:04
further I want to get the special character or number from the name and want to show it as a separate col. eg: Bob@jr , so I want to get '@' in my additional col. — Kaushal Talniya, Jul 28 '20 at 10:44

score 0 · Answer 3 · answered Jul 17 '20 at 14:42

0

You can use following query to include all special characters except space, - and '

SQL> with tbl(name) as (
  2        select 'Kevin#123'          from dual union
  3        select 'bob@jr' from dual union
  4        select 'mike$dr' from dual union
  5        select 'Alex-jr'         from dual union
  6    select 'Brian jr'         from dual union
  7    select 'Brian)jr'         from dual union
  8        select 'Robert''jr'  from dual
  9      )
 10      select *
 11      from tbl
 12      where regexp_like(name, '[^[a-z]|[A-Z]|[:space:]|[:cntrl:]|/]')
 13  and  not regexp_like(name,'[- '']');

NAME
---------
Brian)jr -- see this is included
Kevin#123
bob@jr
mike$dr

SQL>

answered Jul 17 '20 at 14:42

Popeye

35,427
4
10
31

If I am using your first where clause in my code its giving me names which only have alphabets but I want alphabets with special characters always. – Kaushal Talniya Jul 17 '20 at 14:52
what is your first where clause is doing? can you explain please? – Kaushal Talniya Jul 17 '20 at 15:04
I should not get 'Robert' from the below code: WITH tbl(name) AS ( SELECT 'Kevin#123' FROM dual UNION SELECT 'bob@jr' FROM dual UNION SELECT 'mike$dr' FROM dual UNION SELECT 'Alex-jr' FROM dual UNION SELECT 'Brian jr' FROM dual UNION SELECT 'Brian)jr' FROM dual UNION SELECT 'Robert''jr' FROM dual UNION SELECT 'Robert2jr' FROM dual UNION SELECT 'Robert' FROM dual ) SELECT * FROM tbl WHERE regexp_like(name, '[^[a-z]|[A-Z]|[:space:]|[:cntrl:]|/]') AND NOT regexp_like(name,'[- '']'); – Kaushal Talniya Jul 20 '20 at 10:09

How to find a row where col have alphabets,numbers or special characters (except hyphen,apostrophe and space) in Oracle SQL

3 Answers3