Stack Overflow
Stack Overflow

Questions tagged [regexp-substr]

Oracle function that extends the functionality of the SUBSTR function by allowing searching a string for a regular expression pattern. See also REGEXP_INSTR, REGEXP_REPLACE, REGEXP_LIKE and REGEXP_COUNT for other functions extended to use regular expressions.

353 questions
2
votes
1 answer

REGEXP_SUBSTR: Need more precise regex code

I'm trying to find a way to have one regular expression code to only get the 5 digits right before the .(dot). I've tried so many things and can't seem to get the 99999... I'd like to do this without adding any other arguments. SELECT…
FoxArc
  • 114
  • 9
2
votes
2 answers

Oracle SQL: select regexp_substr, "into clause is expected"

I am trying to split a comma delimited string using regexp_substr, but get "into clause is expected" error: select regexp_substr(improv,'[^,]+', 1, level) from dual connect by regexp_substr(improv, '[^,]+', 1, level) is not null; "improv" is a…
Matin
  • 117
  • 5
  • 15
2
votes
2 answers

Oracle sql Join tables based on one table containing the word in the field of another table

I'm trying to join two tables together as follows. Table A Table B Field1 Field1 GO GO FOREGO OK GO I only want to join where the field1 in table A has the exact word as a word in table B, but is not part of another word.…
SomethingClever
  • 57
  • 3
2
votes
3 answers

POSIX ERE Regular expression to find repeated substring

I have a set of strings containing a minimum of 1 and a maximum of 3 values in a format like this: 123;456;789 123;123;456 123;123;123 123;456;456 123;456;123 I'm trying to write a regular expression so I can find values repeated on the same…
Artemio Ramirez
  • 1,116
  • 1
  • 10
  • 23
2
votes
1 answer

REGEXP_SUBSTR - "substring out of bounds" error

I have select col1, ( REGEXP_SUBSTR ( col2, ' ( ?<=~ ) .*? ( ?=ABCD ) ' ) || SUBSTRING ( col2 FROM POSITION ( 'ABCD' IN col2 ) FOR POSITION ( '~' IN SUBSTRING ( col2 FROM POSITION ( 'ABCD' IN col2 ) ) ) -1 ) as xyz) from db.table where…
ysl
  • 51
  • 6
2
votes
1 answer

How to extract all hashtags from string by using regexp_substr

I need a regex pattern which extracts all hastags from a tweets in a table. My data like is select regexp_substr('My twwet #HashTag1 and this is the #SecondHashtag sample','#\S+') from dual it only brings #HashTag1 not #SecondHashtag I need a…
ilhan
  • 39
  • 4
2
votes
2 answers

REGEXP_SUBSTR equivalent in SQL Server

I have an Oracle query using SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',',[^,]+,') FROM DUAL; It returns the output as below: , Redwood Shores, I am trying to get the exact same result in SQL Server. I tried to do it as below …
pratham gn
  • 95
  • 1
  • 4
  • 14
2
votes
2 answers

REGEXP_SUBSTR round bracket

I want to divide a semicolon separated string into its parts with PL/SQL. It’s working fine with REGEXP_SUBSTR as long as there’s no round bracket in the string. Example: select…
user1838910
  • 23
  • 6
2
votes
1 answer

Get second occurrence with regexp_substr

I can't achieve to make sqlfiddle work so here is my simple code : select REGEXP_SUBSTR(' EG SUZ SG SIN blabla ', '^(\s?[A-Z]{2} [A-Z]{3}\s?){2}') from dual; I want to get SG SIN. I know REGEXP_SUBSTR got a parameter used to get the nth occurrence…
So4ne
  • 1,124
  • 17
  • 38
2
votes
2 answers

Rearranging a string value with delimiters in a sql select query

I have a column with string value having delimiters as shown below which I use in the select part of sql query. 0040~0040~0040~0040~0040^00~00~00~01~05^100~001~010~011~015^00~00~00~01~05 individual heads are separated using '^' as shown…
iamP
  • 307
  • 1
  • 3
  • 13
2
votes
1 answer

Why this regexp in Oracle SQL loses the beginning of string?

I'm splitting a string p_value using p_delimeter, which may contain one or more symbols (that's why regexp is not like often used [^,]+). In most of the cases the following query works predictably, but I'm dazed with a case when: string p_value…
diziaq
  • 6,881
  • 16
  • 54
  • 96
2
votes
2 answers

Fetching value from Pipe-delimited String using Regex (Oracle)

I have a sample source string like below, which was in pipe delimited format in that the value obr can be at anywhere. I need to get the second value of the pipe from the first occurrence of obr. So for the below source strings the expected would…
arunb2w
  • 1,196
  • 9
  • 28
2
votes
1 answer

using regex_substr to get corresponding value from another column in oracle

I have the below query which works, which gives the nth string corresponding to the key I give in the where clause as names (separator being ##) select names from ( select regexp_substr('a##b##c##d##e##f','[^##]+', 1, level) as names, …
asb
  • 781
  • 2
  • 5
  • 23
2
votes
5 answers

Split string by space and character as delimiter in Oracle with regexp_substr

I'm trying to split a string with regexp_subtr, but i can't make it work. So, first, i have this query select regexp_substr('Helloworld - test!' ,'[[:space:]]-[[:space:]]') from dual which very nicely extracts my delimiter - blank-blank But then,…
aleko_vp
  • 23
  • 1
  • 1
  • 4
2
votes
4 answers

PLSQL show digits from end of the string

I have the following problem. There is a String: There is something 2015.06.06. in the air 1234567 242424 2015.06.07. 12125235 I need to show only just the last date from this string: 2015.06.07. I tried with regexp_substr with insrt but it doesn't…
user4563345
1 2
3
…
23 24