OraFAQ Forum: SQL & PL/SQL » Simple SQL query

Home » SQL & PL/SQL » SQL & PL/SQL » Simple SQL query

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

Simple SQL query [message #1883]

Fri, 07 June 2002 05:19

Vinayak Awasthi
Messages: 19
Registered: October 2001

Junior Member

Hi all,

I have a string 'abcabcccc i love oracle abc'.I want to remove the leading abc's only ie the result i want should be 'ccc i love oracle abc'.

I want this with SQL only (no PLSQL).

Please help me.

Rgds

Report message to a moderator

Re: Simple SQL query [message #1885 is a reply to message #1883]

Fri, 07 June 2002 05:48

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

but this is not a generic sql!~


  1* select substr('abcabcccc i love oracle abc',2*length('abc')+1) from dual
SQL> /

SUBSTR('ABCABCCCCILOV
---------------------
ccc i love oracle abc

Report message to a moderator

Re: Simple SQL query [message #1895 is a reply to message #1883]

Sat, 08 June 2002 12:04

Amit Chauhan
Messages: 74
Registered: July 1999

Member

Hi,
Try this :
SELECT REPLACE(LTRIM(REPLACE('abcabcabccccd abc ', 'abc', CHR(1)), CHR(1)), CHR(1), 'abc')
FROM DUAL
/
What am doing here is, am replacing the string that is to be removed (abc here) to character 1 (CHR(1)), you can use any other character if you want, am just using it assuming this will not appear in the string anywhere. Then I am using LTRIM to remove CHR(1) from left. And then replace back CHR(1) to abc.

Hope that helps.
Thanks
Amit

Report message to a moderator

Re: Simple SQL query [message #1902 is a reply to message #1883]

Sun, 09 June 2002 20:37

Balaji
Messages: 102
Registered: October 2000

Senior Member

hi vinayak
try this out. this statement will take the substr of the string with starting position as the instr of ccc
it doesnt matter how many times the abc appears.

select substr('abcabcabcabcabcabcccc i love oracle abc',instr('abcabcabcabcabcabcccc i love oracle abc','ccc',1)+1
,length('abcabcabcabcabcabcccc i love oracle abc'))from dual

Report message to a moderator

Re: Simple SQL query [message #2733 is a reply to message #1895]

Thu, 08 August 2002 09:51

Faisal
Messages: 16
Registered: November 2001

Junior Member

Can you explain me the Ltrim funstion a little clear it would be better if you explain me giving an example in Access. I used your query in Access but it doesnt work.

Report message to a moderator

Previous Topic:	PL/SQL procedure ending after retreiving n number of rows and not going through the complete table
Next Topic:	Question on order by clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 16:42:41 CDT 2024