RegEx: all rows with trailing and leading space

Question

Sorry, but this is probably just another "where is the error in my regEx?" - the question is, during my attempt to determine all the lines in the address table, where the format "addition to the house number" is incorrect.

I would like to find all the rows where the value violates the data:

• does not start and end (white) space
• can have any letter, including regular space, between the first and last characters
• has a length of at least 1 and no more than 20 characters
• not null

Examples. As a result, there should be lines in which the cols value

• contains a string with leading or trailing spaces, for example 'auch' or 'auch'
• longer than 20 characters

Acceptable results are of type string -1, A, Haus 1, (Gebäude 1).

To do this, I executed the fowolling request:

SELECT *
from table
WHERE col is not null
AND NOT REGEXP_LIKE (col, 'my_reg_ex_see_below');

I tried the following regex ^([\S])([.]{0,18}[\S]{1}})+$, which should ensure that:

• the first character must be present and must not be a space
• the character from the 2nd to the 19th is optional and can be of any type
• if there are at least two characters, the last should not be a space

But in my result, all rows that are not NULL are returned.

Then I tried this regular expression ^([-0-9a-zA-Z])([-0-9a-zA-Z()\s]{0,18})([-0-9a-zA-Z\(\)]{0,1})$(which, as I know, is too strong because it only allows asci letters), but even with this I still get false results. I get the following three lines in the results

• Incorrect results: (Gebäude A)andHaus 1
• : auch ( , )

, , , col . : , paranthes, .

Answer 1

, , . [\S] S \. [.] (.. \.). , ( POSIX , [:space:] ).

^\S(.{0,18}\S)?$

regex

• ^ -
• \S - -
• (.{0,18}\S)? -
  • .{0,18} - 0 18
  • \S - - char
• $ - .

Answer 2

[\S] \, S.

SQL Fiddle

1:

WITH data( str, name ) AS (
  SELECT ' ', 'space' FROM DUAL UNION ALL
  SELECT '\', 'slash' FROM DUAL UNION ALL
  SELECT 's', 's' FROM DUAL UNION ALL
  SELECT 'S', 'S' FROM DUAL UNION ALL
  SELECT 'n', 'n' FROM DUAL UNION ALL
  SELECT CHR(13), 'CR' FROM DUAL UNION ALL
  SELECT CHR(10), 'LF' FROM DUAL
)
SELECT *
FROM   data
WHERE  REGEXP_LIKE( str, '[\S\n]' )

:

| STR |  NAME |
|-----|-------|
|   \ | slash |
|   S |     S |
|   n |     n |

, [\S\n] , \, S n.

:

SELECT *
from   table
WHERE  REGEXP_LIKE (col, '^\S(.{0,18}\S)?$');

^\S(.{0,18}\S)?$ :

• ^
• \S
  ( - )
• ( )? , ​​ , :
  • .{0,18} 0 18
    ( - , , )
    ( - 20 )
  • \S
    ( - )
• $ .