Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace. Add double quote(") to string of values (10.2)
| regexp_replace. Add double quote(") to string of values [message #622044] |
Wed, 20 August 2014 11:19  |
 |
lott42_gmail
Messages: 146
Registered: June 2010
|
Senior Member |
|
|
I sometimes will check a list of values to see if one particular value exists within it.
For example
SELECT decode(instr('(-2, -1, 3)', '3'), 0, 'Not Exists', 'Exists') val
FROM dual;
Result: Exists
but, if I want to see if "2" exists
SELECT decode(instr('(-2, -1, 3)', '2'), 0, 'Not Exists', 'Exists') val
FROM dual;
Result: Exists
I really want only "2" to exist but not "-2" exists
If I try it with double quotes(") it seems to work the way I want it to
SELECT decode(instr('("-2", "-1", "3")', '"2"'), 0, 'Not Exists', 'Exists') val
FROM dual;
Result: Not Exists
My question is this, Is there a regular expression that I can use to put the double quotes around each value. Each value will be listed in a comma delimited string such as '(-2, -1, 3)'. I can handle the other string such as '2' -> '"2"'??
For now, I used this code to do the same thing. It splits the comma delimited string into rows and does a join with the value I'm looking for. I'm not good enough at regex to accomplish this.
SELECT decode(COUNT(test_value.val), 0, 'Not Exists', 'Exists') val
FROM (SELECT TRIM(regexp_substr(upper(val), '[^,]+', 1, LEVEL)) AS val
FROM (SELECT regexp_replace('(-2, -1, 3)', '[()]') val FROM dual)
CONNECT BY LEVEL <= length(regexp_replace(val, '[^,]')) + 1) possible_values,
(SELECT 2 val FROM dual) test_value
WHERE test_value.val = possible_values.val;
Result: Not Exists
|
|
|
|
|
|
|
|
|
|
|
|
| Re: regexp_replace. Add double quote(") to string of values [message #622071 is a reply to message #622044] |
Wed, 20 August 2014 13:59  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
SQL> SELECT CASE
2 WHEN REGEXP_LIKE('(-2, -1, 3)','(^|\(|,| )' || '&value' || '(,|\)|$)') THEN 'Exists'
3 ELSE 'Not Exists'
4 END val
5 FROM dual;
Enter value for value: 3
old 2: WHEN REGEXP_LIKE('(-2, -1, 3)','(^|\(|,| )' || '&value' || '(,|\)|$)') THEN 'Exists'
new 2: WHEN REGEXP_LIKE('(-2, -1, 3)','(^|\(|,| )' || '3' || '(,|\)|$)') THEN 'Exists'
VAL
----------
Exists
SQL> /
Enter value for value: 2
old 2: WHEN REGEXP_LIKE('(-2, -1, 3)','(^|\(|,| )' || '&value' || '(,|\)|$)') THEN 'Exists'
new 2: WHEN REGEXP_LIKE('(-2, -1, 3)','(^|\(|,| )' || '2' || '(,|\)|$)') THEN 'Exists'
VAL
----------
Not Exists
SQL> /
Enter value for value: -2
old 2: WHEN REGEXP_LIKE('(-2, -1, 3)','(^|\(|,| )' || '&value' || '(,|\)|$)') THEN 'Exists'
new 2: WHEN REGEXP_LIKE('(-2, -1, 3)','(^|\(|,| )' || '-2' || '(,|\)|$)') THEN 'Exists'
VAL
----------
Exists
SQL>
SY.
|
|
|
|
|
|
| Re: regexp_replace. Add double quote(") to string of values [message #622091 is a reply to message #622071] |
Thu, 21 August 2014 00:50 |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Juts for information, you don't need to concatenate &value, you can embed it directly in the string:
SQL> SELECT CASE
2 WHEN REGEXP_LIKE('(-2, -1, 3)','(^|\(|,| )&value(,|\)|$)') THEN 'Exists'
3 ELSE 'Not Exists'
4 END val
5 FROM dual;
Enter value for value: 3
VAL
----------
Exists
1 row selected.
SQL> /
Enter value for value: 2
VAL
----------
Not Exists
1 row selected.
SQL> /
Enter value for value: -2
VAL
----------
Exists
1 row selected.
|
|
|
|
|
|
| Re: regexp_replace. Add double quote(") to string of values [message #623809 is a reply to message #622091] |
Mon, 15 September 2014 02:32 |
 |
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
|
Senior Member |
|
|
hi michel/solomon, i was just searching this forum for regexp solutions , and trying to understand and learn , this particular solution i am not able to get it.Can someone explain me the logic.it would be of great help.
REGEXP_LIKE('(-2, -1, 3)','(^|\(|,| )&value(,|\)|$)')
--^| position start with or condition and consider the opening brackets ,| ,closing brackers by escaping them using \
--&value to be searched
-- (,|\) -- to refer the first captured or memorized group again with ","
--please explain.
|
|
|
|
| Re: regexp_replace. Add double quote(") to string of values [message #623871 is a reply to message #623809] |
Mon, 15 September 2014 13:19 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
m.abdulhaq wrote on Mon, 15 September 2014 03:32this particular solution i am not able to get it.
Well, first we identify (in green) groupings (groupings are enclosed in parenthesis) and (in red) pipe characters (pipe is a special character for OR operation):
'(^|\(|,| )&value(,|\)|$)'
First grouping:
'(^|\(|,| )
consists or three patterns:
1. ^ - beginning of line anchor
2. open parenthesis (we escape it with backslash since open parenthesis is a special character in regexp)
3. space
All three patters are "connected" by OR, therefore first grouping reads beginning of line or open parenthesis or space. Similar way second grouping reads comma or close parenthesis or end of line. So whole pattern reads: beginning of line or open parenthesis or space followed by value followed by comma or close parenthesis or end of line.
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 16:04:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
