Get the part of the field 1

[Andrzejek]

I have a field in my table with Paths to some documents

Oracle TableSPROJECT_DIRECTORY_NUMBER[tt]
PSPROJDIR_DIRECTORY_NO

[blue]W:\Projects\[/blue]03026010A00
[blue]W:\Projects2\[/blue]03026010A00\SomethinElse
[blue]W:\Projects\[/blue]03026010345\Contracts
[blue]W:\Projects2\[/blue]6010GHT00\Build
[blue]W:\Projects\[/blue]03026010XYZ\SomeFolder
...
[/tt]
How can I get just the [blue]BLUE[/blue] part of that field once, ie:
[tt][blue]
W:\Projects\
W:\Projects2\[/blue]
[/tt]
I don't want to hard-code it, I just want to get the start of the path from my data.
[tt]
SELECT DISTINCT ??? PSPROJDIR_DIRECTORY_NO ???
FROM PSPROJECT_DIRECTORY_NUMBER
...
[/tt]

Have fun.

---- Andy

 

[SantaMufasa]

Andy,

Here is code that will display everything up through the second backslash ("\"), if it exists, otherwise it displays the entire string:

select * from my_table;

STR
-------------------------------------
W:\Projects\03026010A00
W:\Projects2\03026010A00\SomethinElse
W:\Projects\03026010345\Contracts
W:\Projects2\6010GHT00\Build
W:\Projects\03026010XYZ\SomeFolder
D:\Program Files\Oracle
D:\Program Files
C:

8 rows selected.

select first_part
  from (select substr(str,1,decode(instr(str,'\',1,2),0,length(str),instr(str,'\',1,2))) first_part
          from my_table)
/

FIRST_PART
-----------------
W:\Projects\
W:\Projects2\
W:\Projects\
W:\Projects2\
W:\Projects\
D:\Program Files\
D:\Program Files
C:

8 rows selected.

Let us know if you have questions about the code, or if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Andrzejek]

Thank you, Santa

I new I had to use SubStr, but I could not put my finger on PS/SQL version of (what in VB is) InStr or Mid to get the position of second \

Star for you

Have fun.

---- Andy

 

[SantaMufasa]

My bad, Andy...I forgot to do the equivalent of a DISTINCT:

select first_part
  from (select substr(str,1,decode(instr(str,'\',1,2),0,length(str),instr(str,'\',1,2))) first_part
          from my_table)
 group by first_part
/

FIRST_PART
-----------------
C:
W:\Projects\
W:\Projects2\
D:\Program Files
D:\Program Files\

5 rows selected.[/coded]I hope this helps.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.