PL/SQL :: How To Use OR (|) With REGEXP-SUBSTR

Jan 10, 2013

i would like to know how can i used the OR "|" with REGEXP_SUBSTR

SELECT  REGEXP_SUBSTR('500.90 Oracle Parkway, 12-12-2000 78 Redwood Shores, CA','([0-9]{1,2})-([0-9]{1,2})-([0-9]{4})') "REGEXPR_SUBSTR"  FROM DUAL;this two condition
([0-9]{1,2})  ([0-9]{1,2}) ([0-9]{4})
or this i did it like this

[code]...

View 8 Replies


ADVERTISEMENT

PL/SQL :: Retrieving Not Full Result / Regexp Substr

Nov 5, 2012

when I try to run this code

SQL> select

2 regexp_substr('But , soft! What light through yonder window breaks?',
3 'l[[:alpha:]] {4}') AS result
4 from dual;

I get just this ( R
-)

where the result should be like the full sub string which is ( light) not just R.

View 2 Replies View Related

PL/SQL :: REGEXP-LIKE Condition?

Jul 26, 2012

I would like to use REGEXP_LIKE condition expression with '[:alnum:]' operator in Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 for to check the string is alphanumeric or not.Why do i need: I have xinput field [RAW(24)] which not sure to contains always an alphanumeric string. We should use only when xinput field is an alphanumeric string.

x varchar2(50);
x:=rtrim(ltrim(utl_raw.cast_to_varchar2(xinput)));

how should i use REGEXP_LIKE

select 'TRUE' from dual where REGEXP_LIKE( 'True', '[:alnum:]')
select 'TRUE' from dual where REGEXP_LIKE( 'False%*^tt123.***', '[:alnum:]')

View 4 Replies View Related

SQL & PL/SQL :: Extract Data - REGEXP?

May 25, 2012

How to extract data from the below string.
END OF FILE. ROW COUNT: 23,9
Output should be divided into 3 parts

END OF FILE. ROW COUNT:
23
9

View 8 Replies View Related

SQL & PL/SQL :: REGEXP Digit Extraction?

Oct 25, 2011

I am trying to write a pattern that will return multiple numbers when it finds the string "My String" in a Text such as

Text String:
My String 5 Test Need a good regExp 34 to test multiple 12334 occurrences of the same 566 search string My String 3 hopefully this time My String 7 with three of these'

Expected Output:
5
3
7

I managed to write Regular expression that returns only for the first match as shown below.

select TO_NUMBER(REGEXP_SUBSTR(REGEXP_SUBSTR (vSearchText1, ('My String' d+')), 'd+'))

View 30 Replies View Related

PL/SQL :: Send Regexp Back Reference To A Function?

Jul 23, 2013

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Can I call a function from a regexp back reference? I would like to call SELECT UTL_ENCODE.TEXT_ENCODE (:mytext, 'WE8ISO8859P1', 1) FROM DUAL;
for all text after Password= and before line break WITH t AS (SELECT '[MyDB]
Army=@MyDBarmy
Navy=@MyDBnavy
Airforce=@MyDBairf

[code]...

View 3 Replies View Related

PL/SQL :: Sql To Substr First And Last Name

Sep 29, 2013

how can i subtr first name and last name  select first_name + lastname from employee for example if i have james michael i what to have JMichael

View 4 Replies View Related

SQL & PL/SQL :: How To Incorporate SUBSTR Into The Query

Aug 19, 2010

This piece of code returns this below:

RPAD(LPAD(' ',12, ' ') || SUBSTR(adj_second_line,(INSTR(adj_second_line,'~',1,1) + 1),
(INSTR(adj_second_line,'~',1,2) - INSTR(adj_second_line,'~',1,1)
- 1)),80,' ') ADJ_SECOND_LINE

Current Output ( ADJ#1-2M1YE2 TYPE 20100524 20100624 1MO/0DY )
===============================================================================================

1) I would like to alter that code above and substr the back 68 characters only with the 12 spaces padded in front, and the trailing nulls to total 80 characters staying as is. How would I put the below substr into that piece of code above?

Desired Output ( 20100524 20100624 1MO/0DY )

SUBSTR(adj_second_line,LENGTH(adj_second_line)-69)
===============================================================================================

2) I also would like the take whatever else there is preceding the 69th character counting it from the back and calling the column ADJ_FIRST_LINE.

Desired Output2 (ADJ#1-2M1YE2 TYPE)

No leading or trailing nulls and This is not a fixed amount of characters.
===============================================================================================

Result would be:

ADJ_FIRST_LINE
-----------------
ADJ#1-2M1YE2 TYPE
ADJ_SECOND_LINE
---------------
20100524 20100624 1MO/0DY
===============================================================================================

View 10 Replies View Related

SQL & PL/SQL :: How To Use Substr To Get Required Result

Jan 7, 2013

i have following table

CREATE TABLE THREAD_SHADES
(
ITM_COD NUMBER NOT NULL,
ITM_DES VARCHAR2(250 BYTE) NOT NULL,
)

[Code]...

Result is

ITM_COD ITM_DES
80064186THREAD TEX-105 SHADE# 7921 (1500 MTRS)
80064187THREAD TEX-40 SHADE#7921 (3000 MTRS)
80114482THREAD TEX 40 SHADE C-8762 1500MTR LOCAL
80130541THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS

I want a query which should return only Sahde # from above data result must be as

7921
C-8762
C-8762
C8676

View 9 Replies View Related

SQL & PL/SQL :: How To Use Substr Or Regular Expression

Jan 21, 2011

I have a following table,

CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......

I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z

How do i use Substr and Regular expression here ?

View 11 Replies View Related

How To Use Substr / Instr To Isolate

Aug 28, 2012

i need to isolate the MVNAME schema from the following job;

dbms_refresh.refresh('"SCHEMA"."MVNAME"');

how to use substr/instr to isolate it?so far I have

select substr(what,instr(what,'"',-1,2),15) from dba_jobs;

View 3 Replies View Related

PL/SQL :: Substr Of Date As Column Name

Aug 27, 2013

Can we have substr(date) as column name. I am trying to create a query where in I need to achieve something like this select s.xyz Today,s.abcd "6 Months ago" || to_char(add_months(sysdate,-6),'Mon rrrr') ||')'from sales_tab s.

View 9 Replies View Related

SQL & PL/SQL :: Substr And Instr In Update Statement

Nov 28, 2010

I have a table1 with columns serial no and name as below

serial no name
1 john paul
2 john victor

and i have another table1 with columns serialno,firstname and second name as below

serial no firstname secondname
1
2

I want to update my table2 with the name from table1 and divide and insert in first name and second name as below

serialno firstname secondname
1 john paul
2 john victor

i use the query as below

update table2 set
firstname = (select substr(name, 1, instr(name, ' ', 1) - 1)
from table1
)

but the above query not worked.

View 11 Replies View Related

SQL & PL/SQL :: SUBSTR / INSTR - Isolate MVNAME Schema?

Aug 28, 2012

i need to isolate the MVNAME schema from the following job;

dbms_refresh.refresh('"SCHEMA"."MVNAME"');

how to use substr/instr to isolate it?so far I have

select substr(what,instr(what,'"',-1,2),15) from dba_jobs;

But its proving a bit tricky to nail it.

View 6 Replies View Related

SQL & PL/SQL :: Execute Privileges To DBMS_LOB.SUBSTR Function

Mar 15, 2011

I am trying to execute the PL/SQL block below:

DECLARE
var VARCHAR2(4000);
BEGIN
SELECT DBMS_LOB.SUBSTR(v_clob,4000,1) INTO var FROM test_clob;
END;
** v_clob is a CLOB column in test_clob table.

I get the below error:

wrong number or types of arguments in call to 'SUBSTR'"SYS"."DBMS_LOB"."SUBSTR": invalid identifier...I have execute privileges to DBMS_LOB.SUBSTR function.

View 2 Replies View Related

Client Tools :: How To Have Column Heading As Example Of Substr Function

Jan 23, 2013

I would like to have a column heading as follows in double inverted comma. but sqlplus environment returns column heading length equal to output value.

SQL> select substr('The independence day', 5,12) "Example of substr function" 2 from dual;

Example of s
------------
independence

I know that default column heading length is 30 character long.

but my column heading is less than 30 character long (which is 26)

How can i have column heading as Example of substr function?

View 7 Replies View Related

Client Tools :: Substr Invalid Number Of Parameters

Dec 27, 2012

I am using the following substr and it works fine on Toad but when i am trying to use within an ETL tool, there getting the error:

substr(PBBDT,length(PBBDT)-1)

Calling <substr> with <2> parameters, but <3> are expected.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved