CODEWITHSUNDEEP
regexoracle-database
Raj Bhatia
Raj Bhatia

Reputation: 1108

Oracle query with regex to find records

I have to find all the records have leading or trailing spaces as well as records having underscore(_) with leading or trailing spaces in between the string from a table having a column as shown below:-

Note:- * is representing an space.

place_id    place_name
----------- --------------------
1           *Bilaspur
2           Jaipur*
3           Madhya*Pradesh
4           State*of*Tamilnaddu
5           **Kangra
6           Chandigrah**
7           Himachal_*Pradesh
8           Utar*_Pradesh

From above table using regex oracle query I have to find out all the records except place id 3 and 4.

Upvotes: 1

Views: 63

Answers (3)

Littlefoot
Littlefoot

Reputation: 142705

Yet another option:

SQL> with test (place_id, place_name) as
  2    (select 1, ' Bilaspur'           from dual union
  3     select 2, 'Jaipur '             from dual union
  4     select 3, 'Madhya Pradesh'      from dual union
  5     select 4, 'State of Tamilnaddu' from dual union
  6     select 5, '  Kangra'            from dual union
  7     select 6, 'Chandigrah  '        from dual union
  8     select 7, 'Himachal_ Pradesh'   from dual union
  9     select 8, 'Utar _Pradesh'       from dual
 10    )
 11  select * from test
 12  where regexp_like(place_name, '^ | $|_ | _');

  PLACE_ID PLACE_NAME
---------- -------------------
         1  Bilaspur
         2 Jaipur
         5   Kangra
         6 Chandigrah
         7 Himachal_ Pradesh
         8 Utar _Pradesh

6 rows selected.

SQL>

Such an expression returns values that

  • start with a space ^ or |
  • ends with a space $ or |
  • contains an underline _

Upvotes: 1

MT0
MT0

Reputation: 167842

You don't need regular expressions:

SELECT *
FROM   table_name
WHERE  place_name LIKE ' %'
OR     place_name LIKE '% '
OR     place_name LIKE '% \_%' ESCAPE '\'
OR     place_name LIKE '%\_ %' ESCAPE '\';

Upvotes: 2

Gurmanjot Singh
Gurmanjot Singh

Reputation: 10360

If the name of the table is A, then you might want to try:

Select * from A where REGEXP_LIKE(place_name,'^\s|\s$|\s_|_\s','i')

Click for regex demo

Explanation:

  • ^\s - matches a white-space after the start of the line
  • | - OR
  • \s$ - matches a white-space before the end of the line
  • | - OR
  • \s_ - matches a white-space before an _
  • | - OR
  • _\s - matches a white-space after an _

Also see the Reference

Upvotes: 2

Related Questions