OraFAQ Forum: Training & Certification » How to find out a specific character in a string

Home » Other » Training & Certification » How to find out a specific character in a string

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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'

Report message to a moderator

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

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

INSTR

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator

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

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).

Report message to a moderator

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

Report message to a moderator

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

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

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

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Michel ,

Please be polite with your reply

Mad

rajuvan

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

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

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

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

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 #283114 is a reply to message #283111]

Mon, 26 November 2007 02:49

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

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

I call it humour and sarcasm.

Regards
Michel

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

Report message to a moderator

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

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

Report message to a moderator

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: 68643
Registered: March 2007
Location: Nanterre, France, http://...

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

balcode
Messages: 4
Registered: November 2007

Junior Member

Thanks everybody!

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Wonderful, this is the best solution posted till now.

Regards
Michel

Report message to a moderator

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

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Well , You have to agree that now Laughing

Rajuvan

Report message to a moderator

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

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Sorry I forgot the smiley: /forum/fa/1693/0/

Regards
Michel

Report message to a moderator

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

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.

Report message to a moderator

Previous Topic:	Oracle/ Sqlplus help
Next Topic:	Oracle Apps :Certifications ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 21:36:44 CDT 2024