Home » SQL & PL/SQL » SQL & PL/SQL » Select NULL using REGEXP_SUBSTR (Oracle 11g)
| Select NULL using REGEXP_SUBSTR [message #492782] |
Wed, 02 February 2011 08:36  |
 |
sinpeak
Messages: 59
Registered: January 2011
Location: india
|
Member |
|
|
Hi,
My Database is in Oracle 11g.
My query returns the 3rd field from a CSV string. If the third field in the string is empty I want the select to return a null but it returns the 4th field :
SELECT REGEXP_SUBSTR( 'A,B,,D,E','[^,]+',1,3) from dual;
.. this returns 'D'.
Can we somehow make the REGEX_SUBSTR return a NULL for the third field ?
Thanks.
|
|
|
|
| Re: Select NULL using REGEXP_SUBSTR [message #492784 is a reply to message #492782] |
Wed, 02 February 2011 08:59  |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> select regexp_replace('A,B,,D,E','^[^,]*,[^,]*,([^,]*),.*$','\1') from dual;
R
-
SQL> select regexp_replace('A,B,C,D,E','^[^,]*,[^,]*,([^,]*),.*$','\1') from dual;
R
-
C
Regards
Michel
|
|
|
|
|
|
| Re: Select NULL using REGEXP_SUBSTR [message #492793 is a reply to message #492792] |
Wed, 02 February 2011 10:41 |
|
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> def pos=1
SQL> select regexp_replace('A,B,,D,E','^([^,]*),([^,]*),([^,]*),([^,]*).*$','\&pos') from dual;
R
-
A
1 row selected.
SQL> def pos=2
SQL> /
R
-
B
1 row selected.
SQL> def pos=3
SQL> /
R
-
1 row selected.
SQL> def pos=4
SQL> /
R
-
D
1 row selected.
Each expression between () is a value you can access using \n (where n is the position of the () expression) in the last parameter.
Regards
Michel
|
|
|
|
| Re: Select NULL using REGEXP_SUBSTR [message #492794 is a reply to message #492792] |
Wed, 02 February 2011 10:43 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You can use normal replace to turn into then trim the result. You have to do it twice since a single pass won't cope with . So I've added an extra comma for my example:
SQL> SELECT trim(REGEXP_SUBSTR(replace(replace('A,B,,,D,E'
2 , ',,'
3 , ', ,'
4 )
5 , ',,'
6 ,', ,'
7 )
8 , '[^,]+'
9 ,1,
10 3)
11 )
12 from dual;
T
-
SQL> SELECT trim(REGEXP_SUBSTR(replace(replace('A,B,,,D,E'
2 , ',,'
3 , ', ,'
4 )
5 , ',,'
6 ,', ,'
7 )
8 , '[^,]+'
9 ,1,
10 5)
11 )
12 from dual;
T
-
D
|
|
|
|
| Re: Select NULL using REGEXP_SUBSTR [message #492800 is a reply to message #492794] |
Wed, 02 February 2011 11:09 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Since the result gets trimmed anyway, you could also just replace "," with ", " in one go, I guess :
SQL> SELECT trim(REGEXP_SUBSTR(
2 replace('A,B,,,D,E', ',', ', '), '[^,]+',1,2))
3 from dual;
T
-
B
SQL> SELECT trim(REGEXP_SUBSTR(
2 replace('A,B,,,D,E', ',', ', '), '[^,]+',1,3))
3 from dual;
T
-
SQL> SELECT trim(REGEXP_SUBSTR(
2 replace('A,B,,,D,E', ',', ', '), '[^,]+',1,5))
3 from dual;
T
-
D
|
|
|
|
| Re: Select NULL using REGEXP_SUBSTR [message #492802 is a reply to message #492794] |
Wed, 02 February 2011 11:22 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
with t as (
select 'A,B,,D,E' str from dual
)
select regexp_substr(',' || str,',([^,]*)',1,1,null,1) first_element,
regexp_substr(',' || str,',([^,]*)',1,2,null,1) second_element,
regexp_substr(',' || str,',([^,]*)',1,3,null,1) third_element,
regexp_substr(',' || str,',([^,]*)',1,4,null,1) forth_element,
regexp_substr(',' || str,',([^,]*)',1,5,null,1) fifth_element
from t
/
F S T F F
- - - - -
A B D E
SQL>
SY.
|
|
|
|
| Re: Select NULL using REGEXP_SUBSTR [message #492806 is a reply to message #492800] |
Wed, 02 February 2011 11:31 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ThomasG wrote on Wed, 02 February 2011 12:09Since the result gets trimmed anyway, you could also just replace "," with ", " in one go, I guess
I do not see TRIM in original post. What I did see - OP is on 11g where REGEXP_SUBSTR finally got additional parameter - subexpr. Therefore - no need to replace ',' with ', '.
SY.
|
|
|
|
|
|
| Re: Select NULL using REGEXP_SUBSTR [message #492812 is a reply to message #492808] |
Wed, 02 February 2011 12:47 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Another option is to create your own generic function using good old substr and instr, then use that instead of regexp, as shown below.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION list_element
2 (p_string IN VARCHAR2,
3 p_element IN NUMBER,
4 p_delim IN VARCHAR2 DEFAULT ',')
5 RETURN VARCHAR2
6 AS
7 v_string VARCHAR2 (32767) := p_delim || p_string || p_delim;
8 BEGIN
9 RETURN SUBSTR
10 (v_string,
11 INSTR (v_string, p_delim, 1, p_element) + LENGTH (p_delim),
12 INSTR (v_string, p_delim, 1, p_element + 1)
13 - INSTR (v_string, p_delim, 1, p_element) - LENGTH (p_delim));
14 END list_element;
15 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> SET NULL Null
SCOTT@orcl_11gR2> SELECT list_element ('A,B,,D,E', 1) FROM DUAL
2 /
LIST_ELEMENT('A,B,,D,E',1)
--------------------------------------------------------------------------------
A
1 row selected.
SCOTT@orcl_11gR2> SELECT list_element ('A,B,,D,E', 2) FROM DUAL
2 /
LIST_ELEMENT('A,B,,D,E',2)
--------------------------------------------------------------------------------
B
1 row selected.
SCOTT@orcl_11gR2> SELECT list_element ('A,B,,D,E', 3) FROM DUAL
2 /
LIST_ELEMENT('A,B,,D,E',3)
--------------------------------------------------------------------------------
Null
1 row selected.
SCOTT@orcl_11gR2> SELECT list_element ('A,B,,D,E', 4) FROM DUAL
2 /
LIST_ELEMENT('A,B,,D,E',4)
--------------------------------------------------------------------------------
D
1 row selected.
SCOTT@orcl_11gR2> SELECT list_element ('A,B,,D,E', 5) FROM DUAL
2 /
LIST_ELEMENT('A,B,,D,E',5)
--------------------------------------------------------------------------------
E
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Select NULL using REGEXP_SUBSTR [message #492814 is a reply to message #492812] |
Wed, 02 February 2011 13:07 |
|
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Keeping in memory that calling a custom PL/SQL function is slower than built-in ones (although I will not bet on this for 10g regexp functions, but 11g are far better ones).
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Wed Aug 20 23:44:33 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
