CODEWITHSUNDEEP
sqlregexoracle-databasenumberswhitespace
Zbigniew
Zbigniew

Reputation: 11

finding numbers with spaces using regex

I need to use regex_count in Oracle SQL (no \b available) and I need to count values (in format (\d{1,}[a-zA-Z]{0,2}) (like 123, 123a, 34bc, but not 32abc) in a string which are separated from each other (or other noises) by at least one space, so example

'34 64 84' - should count to 3
'34 fh84h d88f 34 ' - should count to 2
'a54. 374 4/5' - should count 1
'5,5' - should count 0
'10a' - should count 1
'20g 32lf 43vnd kf34ld' - count 2
'9A_10' - counts 0

Something like that.

(^|\s|\b)(\d{1,}[a-zA-Z]{0,2})($|\s) - but these is no \b in Oracle SQL and anyway I am not sure if it works.

Anyone with suggestions?

Upvotes: 1

Views: 829

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626802

I suggest duplicating each whitespace and add leading/trailing spaces and then run a simple (^|\s)<YOUR_PATTERN>($|\s) pattern:

SELECT REGEXP_COUNT(' ' ||  REGEXP_REPLACE('a54. 374 4/5', '(\s)', '\1 ') || ' ',  '(^|\s)\d+[a-zA-Z]{0,2}($|\s)') FROM DUAL

Details

  • ' ' || - adding a leading space
  • REGEXP_REPLACE('a54. 374 4/5', '(\s)', '\1 ') - match and capture any whitespace and replace with itself plus a space
  • || ' ' - adding a trailing space.

Now, your count will be correct when running a (^|\s)\d+[a-zA-Z]{0,2}($|\s) regex (note that + is equal to {1,}).

  • (^|\s) - start of string, whitespace
  • \d+ - 1+ digits
  • [a-zA-Z]{0,2} - 0 to 2 letters
  • ($|\s) - end of string or whitespace.

Upvotes: 4

Related Questions