CODEWITHSUNDEEP
oracle-database
Anirudh S
Anirudh S

Reputation: 41

Getting data from a column only if it contains number or special characters in Oracle

I want data from a column only if it contains special characters or numbers. My query looks as follows:

Select First_name from account where regexp_like(First_name,'[0-9]')

But I don't know how to achieve that special character thing

Upvotes: 2

Views: 578

Answers (2)

Littlefoot
Littlefoot

Reputation: 142720

How about upside-down approach? Select values that aren't all letters. How? Remove them from the string! Something like this:

SQL> with account (id, first_name) as
  2    (select 1, 'Little'     from dual union all   -- valid
  3     select 2, 'der Leyen'  from dual union all   -- valid
  4     select 3, 'F00t'       from dual union all   -- invalid; two zeros
  5     select 4, 'Sco#tt'     from dual union all   -- invalid; #
  6     select 5, 'Me@SO'      from dual union all   -- invalid; @
  7     select 6, 'What_is_it' from dual union all   -- invalid; _
  8     select 7, '12.345'     from dual union all   -- invalid; digits
  9     select 8, 'Huh? Whoa!' from dual             -- invalid; ?!
 10    )
 11  select id,
 12    first_name,
 13    regexp_replace(first_name, '[[:alpha:] ]', null) repl
 14  from account
 15  where regexp_replace(first_name, '[[:alpha:] ]', null) is not null
 16  order by id;

        ID FIRST_NAME REPL
---------- ---------- ----------
         3 F00t       00
         4 Sco#tt     #
         5 Me@SO      @
         6 What_is_it __
         7 12.345     12.345
         8 Huh? Whoa! ?!

6 rows selected.

SQL>

Column REPL is here to show what's left after replacing letters (and a space) with null; you wouldn't normally display it.

Upvotes: 0

Sentinel
Sentinel

Reputation: 6449

Well you're most of the way there already, just add the special characters you are interested in to the character class you've already defined in the regexp:

Select First_name
  from account
 where regexp_like(First_name,'[0-9@#$_!?*]')

To also select records where First_name is null use one of the two following queries:

Select First_name
  from account
 where regexp_like(First_name,'[0-9@#$_!?*]')
    or First_name is null

or

Select First_name
  from account
 where regexp_like(nvl(First_name,'!'),'[0-9@#$_!?*]')

The first one explicitly selects rows where First_name is null while the second query gets it by substituting a special character string for null strings.

Upvotes: 2

Related Questions