CODEWITHSUNDEEP
sqlregexoracle-databaseregexp-substr
dreambigcoder
dreambigcoder

Reputation: 1907

SQL regex expression for text before pipe

I need an oracle regex to fetch data before first pipe and after the last slash from the text before pipe.

For example, from the string:

test=file://2019/13/40/9/53/**2abc123-7test-1edf-9xyz-12345678.bin**|type

the data to be fetched is:

2abc123-7test-1edf-9xyz-12345678.bin

Upvotes: 0

Views: 1263

Answers (4)

revo
revo

Reputation: 48751

You may use:

REGEXP_SUBSTR(column, '/([^/|]+)\|', 1, 1, NULL, 1)

Live demo here

Regex breakdown:

  • / Match literally
  • ( Start of capturing group #1
    • [^/|]+ Match anything except slash and pipe, at least one character
  • ) End of CG #1
  • \| Match a pipe

Upvotes: 1

LukStorms
LukStorms

Reputation: 29667

This works in Oracle : 

select regexp_substr(col,'[^|/]+\.\w+',1,1,'i')
from (
    select 'test=file://2019/13/40/9/53/2abc123-7test-1edf-9xyz-12345678.bin|type=app/href|size=1234|encoding=|locale=en_|foo.bar' as col 
    from dual
) q

MySql & TeraData also have such REGEXP_SUBSTR function, but haven't tested it on those.

Upvotes: 2

Katajun
Katajun

Reputation: 473

[^\/]*?(?=\|)

  • [^\/]*? — matches any character that is not a backslash

  • (?=\|) — positive lookahead to match a vertical line

Upvotes: 0

Matthew Moore
Matthew Moore

Reputation: 866

The pattern ^.+?/([^/]+?)\| starts at the beginning of the string, skips over every character, then captures all non-slash characters, between the last slash and the first pipe.

Upvotes: 1

Related Questions