Home » SQL & PL/SQL » SQL & PL/SQL » INSTR
INSTR [message #563400] Mon, 13 August 2012 09:28 Go to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
Hello,

sorry normally I work wih SQL Server, but now it have to with orcacle. It seems I don't know anything about SQL:)

Are such queries possible?

select INSTR('.', 'AA-1057_D02_Anhang 7 Version 01.docx')


just to proof whether it works or not

Here is my first example:

select * from table where name =  substr('Version 01.docx',0,INSTR('.', 'Version 01.docx'))+'.pdf'


It doesn't work..what might be the problem?

Kind regards,

Lara
Re: INSTR [message #563402 is a reply to message #563400] Mon, 13 August 2012 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
>It doesn't work..what might be the problem?

see a picture of my car.
It does not work
what might be problem?

What exactly are you trying to do?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions080.htm#SQLRF00651

[Updated on: Mon, 13 August 2012 09:40]

Report message to a moderator

Re: INSTR [message #563405 is a reply to message #563400] Mon, 13 August 2012 09:48 Go to previous messageGo to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
hello,

I see the car and thanks for the warm welcome:)

Lets get back to the problem:

select INSTR('.', 'Version 01.docx')


what I want is the position of the "."

In Sql Server the result would be a number....is that possible to check queries in that way in oracle?


select * from table where name =  substr('Version 01.docx',0,INSTR('.', 'Version 01.docx'))+'.pdf'


the expected result should be the entry where the name = 'Version 01.pdf'

All what I try doing is to replace the prefix docx with pdf

I hope you can give me some hints..

Kind regards,

Lara
Re: INSTR [message #563406 is a reply to message #563405] Mon, 13 August 2012 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
the "string" & "substring" are reversed.
  1* select INSTR('Version 01.docx','.') from dual
07:55:33 SQL> /

INSTR('VERSION01.DOCX','.')
---------------------------
			 11

07:55:34 SQL> 

Re: INSTR [message #563407 is a reply to message #563400] Mon, 13 August 2012 09:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2031
Registered: January 2010
Senior Member
musclebreast wrote on Mon, 13 August 2012 10:28
It seems I don't know anything about SQL:)


Well, when I know nothing about a subject I start from documentation. Oracle function INSTR first parameter is string to be searched. And numbering starts with 1, not with 0. And concatenation operator is not plus but ||. So obviously query:

SQL> select substr('Version 01.docx',1,INSTR('.', 'Version 01.docx')) || '.pdf' from dual;

SUBS
----
.pdf

SQL> 


will return .pdf while query:

SQL> select substr('Version 01.docx',1,INSTR('Version 01.docx','.')) || 'pdf' from dual;

SUBSTR('VERSIO
--------------
Version 01.pdf

SQL> 


will return what, I believe, you are looking for.

SY.
Re: INSTR [message #563409 is a reply to message #563407] Mon, 13 August 2012 10:20 Go to previous messageGo to next message
musclebreast
Messages: 24
Registered: July 2012
Junior Member
Hello guys,

thanks. I get used to oracle slightly..with these basics I can start working and yes I'll read more carefully the documentation of oracle.

Kind regards,

Lara

[Updated on: Mon, 13 August 2012 10:24]

Report message to a moderator

Re: INSTR [message #563412 is a reply to message #563409] Mon, 13 August 2012 10:50 Go to previous message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
substr('Version 01.docx',0,INSTR('.', 'Version 01.docx'))+'.pdf'


Indice start at 1 in Oracle string.

All Oracle documentation is online, for instance: Database SQL Reference.

Regards
Michel

[Updated on: Mon, 13 August 2012 10:52]

Report message to a moderator

Previous Topic: Import CSV in clob to table
Next Topic: Updating Accounts and Displaying Changes
Goto Forum:
  


Current Time: Fri Sep 19 11:09:07 CDT 2014

Total time taken to generate the page: 0.08496 seconds