Questions tagged [regexp-like]

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.

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…
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…
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 …
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,…
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 …
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]+)$'));