CODEWITHSUNDEEP
sqloracle-database
jerry
jerry

Reputation: 5

how to fetch specific number of characters before the pattern

I have values like the below in my table:

SER : 3-576509910214, 4182 5979WM

I need to remove the white spaces first. then fetch the 8 numbers before or after the occurrence of matching string 'WM'. I need the output for the above value as '41825979'. I need to fetch 8 numbers after each occurrence of 'WM'.

WM can occur anywhere in the string.

How can I do that in with an Oracle SQL query?

Upvotes: 0

Views: 149

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This will return a string of digits up to 8 long from such a string:

select replace(regexp_substr(replace(str, ' ', ''), '[0-9]{1,8}WM'), 'WM', '')

If you want before and after, just modify the pattern:

select replace(regexp_substr(replace(str, ' ', ''), '[0-9]{1,8}WM|WM[0-9]{1-8}'), 'WM', '')

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

To take 8 digits before or after WM (after spaces are removed), use following:

WITH Demo(t) AS
(
  SELECT 'SER : 3-576509910214, 4182 5979WM' FROM dual
  UNION ALL
  SELECT 'SER : 3-576509910214, 4182 5 979 WM' FROM dual
  UNION ALL
  SELECT 'SER : 3-576509910214,WM 4182 5979' FROM dual
)
SELECT
  REPLACE(COALESCE(
    REGEXP_SUBSTR(REPLACE(t, ' ', ''), '[0-9]{8}WM'),
    REGEXP_SUBSTR(REPLACE(t, ' ', ''), 'WM[0-9]{8}')
  ), 'WM', '')
FROM Demo

Upvotes: 0

Related Questions