Home » Other » Training & Certification » How to find out a specific character in a string
How to find out a specific character in a string [message #282973] Sun, 25 November 2007 14:28 Go to next message
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 #282974 is a reply to message #282973] Sun, 25 November 2007 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
INSTR

Regards
Michel
Re: How to find out a specific character in a string [message #283009 is a reply to message #282973] Sun, 25 November 2007 23:18 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

SELECT INSTR ('TEST', 'E')
  FROM DUAL;


Note :- It returns the position of 'E' in TEST string.

Regards,
Kiran
Re: How to find out a specific character in a string [message #283011 is a reply to message #282973] Sun, 25 November 2007 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT INSTR ('GEESE', 'E') FROM DUAL;
Note :- It returns the position of 'E' in GEESE string
(Some examples are better than others).


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 Go to previous messageGo to next message
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>


Thumbs Up
Rajuvan
Re: How to find out a specific character in a string [message #283070 is a reply to message #283059] Mon, 26 November 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is the purpose of giving such stupid complex query when a built-in function exist?

Oh yes! I see it is in your line of posting redundant, useless and incomplete answers.

Regards
Michel
Re: How to find out a specific character in a string [message #283077 is a reply to message #282973] Mon, 26 November 2007 01:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Just trying to give One more option ( you call it developer's creativity) Smile

I know it is a complex query when there is built-in function exists. But I dont think it is a STUPID query.

Thumbs Up
Rajuvan.
Re: How to find out a specific character in a string [message #283091 is a reply to message #283077] Mon, 26 November 2007 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
When you can do something easily and simply and do it in a complex and less perfomant way, I name it stupid.
I don't say it is "developer's creativity", I say it is masturbation.

Regards
Michel
Re: How to find out a specific character in a string [message #283106 is a reply to message #282973] Mon, 26 November 2007 02:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Michel ,

Please be polite with your reply

Mad
rajuvan
Re: How to find out a specific character in a string [message #283107 is a reply to message #283091] Mon, 26 November 2007 02:32 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Another variant:
SQL> var thetext VARCHAR2(30)
SQL> exec :thetext := 'Maaher'

PL/SQL procedure successfully completed.

SQL>
SQL> 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
--------------
Exists

MHE

Very Happy
Re: How to find out a specific character in a string [message #283109 is a reply to message #283106] Mon, 26 November 2007 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Well, I promise to be polite (although I don't see why it was not polite) as soon as you'll stop to post stupid, redundant or useless answer.

Regards
Michel
Re: How to find out a specific character in a string [message #283111 is a reply to message #282973] Mon, 26 November 2007 02:38 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Maher,

One more Nice logic .

@ Michel,

Will you call Maher's answer also '----' ?

Thumbs Up
Rajuvan.

[Updated on: Mon, 26 November 2007 02:40]

Report message to a moderator

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 Go to previous messageGo to next message
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.


Very Happy
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 #283114 is a reply to message #283111] Mon, 26 November 2007 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

Will you call Maher's answer also '----' ?

I call it humour and sarcasm.

Regards
Michel
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 Go to previous messageGo to next message
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>


Thumbs Up
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 Go to previous messageGo to next message
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 #283133 is a reply to message #283123] Mon, 26 November 2007 03:18 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Model clause hey?

SQL> var thetext varchar2(20)
SQL> exec :thetext := 'Michel'

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT DECODE(count(*), 0, 'Doesn''t exist', 'it exists') does_it_exist
  2  FROM   ( SELECT substr ( txt, lvl, 1 )  as token
  3           FROM   (select :thetext txt from dual)
  4           model  DIMENSION BY ( 1 AS dm )
  5                  MEASURES ( 1 AS lvl )
  6                  RULES UPSERT ( lvl[ FOR dm FROM 1 TO LENGTH(:thetext)
  7                                      INCREMENT 1 ] = cv(dm) )
  8         )
  9  WHERE  token = 'e'
 10  /

DOES_IT_EXIST
-------------
it exists
Granted, your model solution seems more readable.

MHE
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 Go to previous messageGo to next message
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

Re: How to find out a specific character in a string [message #283656 is a reply to message #283059] Tue, 27 November 2007 20:58 Go to previous messageGo to next message
balcode
Messages: 4
Registered: November 2007
Junior Member
Thanks everybody!
Re: How to find out a specific character in a string [message #283657 is a reply to message #282973] Tue, 27 November 2007 21:08 Go to previous messageGo to next message
balcode
Messages: 4
Registered: November 2007
Junior Member
SQL> SELECT DECODE(INSTR('TEST','J'),0,'Not Exists','Exists') FROM DUAL;

DECODE(INS
----------
Not Exists


SQL> SELECT DECODE(INSTR('TEST','T'),0,'Not Exists','Exists') FROM DUAL;

DECODE
------
Exists
Re: How to find out a specific character in a string [message #283677 is a reply to message #283657] Tue, 27 November 2007 23:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Wonderful, this is the best solution posted till now.

Regards
Michel
Re: How to find out a specific character in a string [message #283680 is a reply to message #282973] Tue, 27 November 2007 23:22 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Well , You have to agree that now Laughing Wink

Thumbs Up
Rajuvan
Re: How to find out a specific character in a string [message #283704 is a reply to message #283680] Wed, 28 November 2007 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sorry I forgot the smiley: /forum/fa/1693/0/

Regards
Michel
Re: How to find out a specific character in a string [message #283721 is a reply to message #282973] Wed, 28 November 2007 00:52 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I know . You forgot the smiley.

Anyway for Newbies like 'balcode' , model, dimension etc will be Indigestible stuff . They prefer queries that seems more readable as reported by 'Maaher'.

Thumbs Up
Rajuvan.
Previous Topic: Oracle/ Sqlplus help
Next Topic: Oracle Apps :Certifications ?
Goto Forum:
  


Current Time: Sun Dec 08 05:55:30 CST 2024