How to find out a specific character in a string [message #282973] |
Sun, 25 November 2007 14:28 |
balcode
Messages: 4 Registered: November 2007
|
Junior Member |
|
|
How to find out a specific character in a string.
how can I find out character 'E' in a string 'TEST'.
Is there any way to confirm whether 'E' is there in the string. Or is there any way to find out the position of 'E'
|
|
|
|
|
|
Re: How to find out a specific character in a string [message #283059 is a reply to message #282973] |
Mon, 26 November 2007 00:57 |
|
rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India
|
Senior Member |
|
|
You can use something as below for getting the presence of character in a string.
SQL> SELECT SIGN(LENGTH('RAJUVAN')-NVL(LENGTH(REPLACE('RAJUVAN', 'U')),0)) PRESENT_OR_NOT
2 FROM DUAL;
PRESENT_OR_NOT
--------------
1
SQL> SELECT SIGN(LENGTH('RAJUVAN')-NVL(LENGTH(REPLACE('RAJUVAN', 'E')),0)) PRESENT_OR_NOT
2 FROM DUAL;
PRESENT_OR_NOT
--------------
0
SQL> SELECT SIGN(LENGTH('RAJUVAN')-NVL(LENGTH(REPLACE('RAJUVAN', 'VAN')),0)) PRESENT_OR_NOT
2 FROM DUAL;
PRESENT_OR_NOT
--------------
1
SQL> SELECT SIGN(LENGTH('RAJUVAN')-NVL(LENGTH(REPLACE('RAJUVAN', 'BUS')),0)) PRESENT_OR_NOT
2 FROM DUAL;
PRESENT_OR_NOT
--------------
0
SQL>
Rajuvan
|
|
|
|
|
|
|
|
|
|
Re: How to find out a specific character in a string [message #283113 is a reply to message #283107] |
Mon, 26 November 2007 02:48 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> var thetext varchar2(20)
SQL> exec :thetext := 'Michel'
PL/SQL procedure successfully completed.
SQL> select fl e_idx
2 from ( select fl
3 from ( select level idx, substr(:theText,level,1) letter
4 from dual connect by level <= length(:thetext) )
5 model
6 dimension by (idx)
7 measures (letter, to_number(null) fl)
8 rules (fl[ANY] = decode(letter[cv()],'e',cv(idx))))
9 where fl is not null
10 /
E_IDX
----------
5
1 row selected.
Michel
[Updated on: Mon, 26 November 2007 03:06] Report message to a moderator
|
|
|
|
Re: How to find out a specific character in a string [message #283123 is a reply to message #282973] |
Mon, 26 November 2007 03:05 |
|
rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India
|
Senior Member |
|
|
OK.. fine
Then One more variant (Going back to INSTR)
Michel .. Treat this also Humour
SQL> SELECT DECODE(INSTR('Michel Cadot','hel'),0,'Does not exist','Exists') PRES
ENT_OR_NOT
2 FROM DUAL;
PRESEN
------
Exists
SQL> SELECT DECODE(INSTR('Michel Cadot','hell'),0,'Does not exist','Exists') PRE
SENT_OR_NOT
2 FROM DUAL;
PRESENT_OR_NOT
--------------
Does not exist
SQL>
Rajuvan.
[Updated on: Mon, 26 November 2007 03:06] Report message to a moderator
|
|
|
Re: How to find out a specific character in a string [message #283126 is a reply to message #282973] |
Mon, 26 November 2007 03:09 |
muzahid
Messages: 281 Registered: September 2004 Location: Dhaka, Bangladesh
|
Senior Member |
|
|
Using oracle built-in function
TEST@prod>SELECT INSTR ('TEST', 'A')
2 FROM DUAL;
INSTR('TEST','A')
-----------------
0
Using Rajuvan's query
TEST@prod>SELECT SIGN(LENGTH('TEST')-NVL(LENGTH(REPLACE('TEST', 'A')),0)) PRESENT_OR_NOT
2 FROM DUAL;
PRESENT_OR_NOT
--------------
0
Using Maheer's query
TEST@prod>var thetext VARCHAR2(30)
TEST@prod>exec :thetext := 'Test'
PL/SQL procedure successfully completed.
TEST@prod> SELECT decode( count(letter)
2 , 0, 'Does not exist'
3 , 'Exists'
4 ) does_it_exist
5 FROM ( SELECT SUBSTR(:thetext,LEVEL, 1) letter
6 FROM dual
7 CONNECT BY LEVEL <= LENGTH(:thetext)
8 )
9 WHERE letter = 'a'
10 /
DOES_IT_EXIST
--------------
Does not exist
And at last micheal's query
TEST@prod>select fl e_idx
2 from ( select fl
3 from ( select level idx, substr(:theText,level,1) letter
4 from dual connect by level <= length(:thetext) )
5 model
6 dimension by (idx)
7 measures (letter, to_number(null) fl)
8 rules (fl[ANY] = decode(letter[cv()],'a',cv(idx))))
9 where fl is not null;
no rows selected
|
|
|
|
Re: How to find out a specific character in a string [message #283141 is a reply to message #283133] |
Mon, 26 November 2007 03:31 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Model is endless:
SQL> var thetext varchar2(10)
SQL> exec :thetext := 'Michel'
PL/SQL procedure successfully completed.
SQL> select e_idx
2 from ( select decode(substr(:thetext,idx,1),'e',idx) e_idx
3 from dual
4 model
5 dimension by (1 dm)
6 measures (1 idx)
7 rules iterate (10) (idx[iteration_number] = iteration_number+1)
8 )
9 where e_idx is not null
10 /
E_IDX
----------
5
1 row selected.
Regards
Michel
[Updated on: Mon, 26 November 2007 03:32] Report message to a moderator
|
|
|
|
|
|
|
|
|