Oracle function similar to the LIKE condition, but REGEXP_LIKE performs regular expression pattern matching. See also REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR and REGEXP_COUNT for other functions extended to use regular expressions.
Questions tagged [regexp-like]
199 questions
0
votes
2 answers
REGEXP_LIKE for anything not like MM/DD/YYYY
What is the regular expression to be used to catch anything in the database which is not of the expression MM/DD/YYYY.
(I want everything which is apart from the above mentioned format, could be dd-mon-yy or yyyy/mm/dd etc)
I am using the below…

Shubham Sharan
- 23
- 2
- 7
0
votes
1 answer
How to use Oracle regexp_like to identify rows where column contains any of list of characters
I have a table where I need to identify rows where a column named LONGDSC contains any of the following characters _*%?:;=()[]/\|#."
I have tried many different iterations, but I am having no success. I would appreciate any assistance.
select *…

sf_gray
- 13
- 4
0
votes
2 answers
how count results from a multiple regexp oracle
I am having to do a regexp_like and list over 130 different checks.
at the moment I am getting all the fields in the table that do match.
but in the results table I just want a list of the searches and a count next to them.
below is
SELECT *
FROM…

FaisalH
- 1
0
votes
1 answer
Alternative to REGEXP_LIKE due to regular expression too long
I am getting an ORA-12733: regular expression too long error when trying to find if certain ids already inside the database.
regexp_like (','||a.IDs||',',',('||replace(b.IDs,',','|')||'),')
a.IDs and b.IDs are in a format of something like…

Fiona
- 85
- 1
- 7
0
votes
1 answer
Regexp_replace and regexp_like for Email
I use this regex for extract email:
regexp_replace(xxx, '.*=([A-Za-z0-9._%-]*@[A-Za-z0-9._%-]*\.[A-Za-z]{2,4}).*','\1')
regexp_like(xxx,'=+[A-Za-z0-9._%-]+@[A-Za-z0-9._%-]+\.[A-Za-z]{2,4}')
I can extract string like this:
select…

onur
- 5,647
- 3
- 23
- 46
0
votes
1 answer
REGEXP_REPLACE query oracle
I have a text like following.
( ( tt.TRAN_TYPE IN
( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1332 )
AND tt.CONTRACT IN
( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1333 )
AND tt.CUSTOMER_STATE NOT IN
…

Kalpit Joshi
- 9
- 4
0
votes
2 answers
Check Multiple Values in WHERE Clause SQL Query REGEXP_LIKE Oracle 11g
if using REGEXP_LIKE in complex query it take very long time to execute, Can you provide alternate solution for that.
get List of values from one query and should use only those values to another query where clause
LV_TRANS_TYPES_IDS …

Chidam
- 37
- 2
- 12
0
votes
1 answer
SQL: Extract specific part of string
I'm trying to figure out how to extract a specific part of a string (made out of multiple terms) with a select statement in Oracle SQL.
The values in the column look somewhat like '2E WK 12-345-678 TM 13-06-2017', which has a slight variation in…

haassiej
- 55
- 1
- 10
0
votes
0 answers
How to limit special characters in oracle DB
How to limit the special characters from being accepted by specific column.?
can this be done with REGEXP_LIKE ? Apart from alphanumerics below are the special characters to be allowed from the text field and constraints cant be added in…

Devoloper250
- 753
- 2
- 8
- 12
0
votes
2 answers
Oracle split message with regexp_substr
I need to split message:
500 Oracle Parkway.Redwood Shores.*.=13
Now I have a bit worked solution for Substr1/2/4
SELECT '500 Oracle Parkway.Redwood Shores.*.=13' string1,
REGEXP_SUBSTR('500 Oracle Parkway.Redwood Shores.*.=13','.[^.]+')
…

27P
- 1,183
- 16
- 22
0
votes
2 answers
Oracle regexp_like word boundaries multiple words workaround
As you know Oracle POSIX implementation of regexes does not support word boundaries. One workaround is suggested here:
Oracle REGEXP_LIKE and word boundaries
However it does not work if I want to, for instance select all 4 character strings.…

Dimiter
- 37
- 4
0
votes
0 answers
Update Oracle table using functions
I have the below queries and would like to update the corresponding data in the table that the queries return. Is it possible to utilize an update statement with an Oracle function? If so, how?
select *
from A
where translate(transnbr,…

user2607902
- 13
- 5
0
votes
2 answers
Oracle: to_number() combined with substr() and regexp_like() in WHERE clause
I have a table with a column "description" which has the following values:
OPTestMachine
OPManualTesting
OP1010
So the select statement, to get the values, would just be
SELECT description
FROM operation;
I want to extract the number "1010"…

rwur
- 237
- 1
- 6
- 16
0
votes
1 answer
Is there an easy way to apply REGEXP_LIKE to path coming from connect by prior query in Oracle?
I have a query including connect by prior like;
SELECT SYS_CONNECT_BY_PATH(ENAME, '/') as path
FROM EMP
WHERE CONNECT_BY_ISLEAF=1
START WITH MGR IS NULL CONNECT BY
PRIOR EMPNO=MGR;
The result is following
…

user6493966
- 73
- 2
- 8
0
votes
2 answers
counting rows returned by regexp_like
I need to count the number of rows returned by REGEXP_LIKE() in oracle. how do I do that?
I have tried the following query in a function:
RETURN COUNT(REGEXP_LIKE(SIN, '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'));

kavya.jain
- 3
- 2