Get part of a string with SQL 1

[Andrzejek]

I have PROJECT_NUMBER field in MyTable where a part of it is a ROUTE (either 1, 2, or 3 digits long) that I need to retrieve and have it formatted as 3 characters long (well, zero-filled):

[pre]
PROJECT_NUMBER ROUTE
FN-[red]63[/red]-3(47)--21-62 063
STPN-G-[red]123[/red]-2(15)--2J-75 123
NHS-[red]5[/red]-5(41)3--19-77 005
SF-XYZ-[red]61[/red]-5(76)--92-82 061
HS-[red]5[/red]-5(41)12--19-77 005
STP-ABCD-[red]654[/red]-8(43)--2C-17 654
STPN-[red]5[/red]-5(14)--2J-63 005
NHSN-[red]5[/red]-5(67)--2R-77 005
IM-[red]35[/red]-3(73)85--13-77 035
NHS-[red]34[/red]-9(81)--19-29 034
[/pre]
The only 'constant' logic I can see is: if you start from left parenthesis '(' and go left (backwards), the ROUTE is between first and second dash '-'
I know you cannot get the Route from between first and second dash '-' going forward because the 'letter' portion of PROJECT_NUMBER can have a dash in it ('STPN-G', 'STP-ABCD', etc.)

So, what would be the SQL...

[tt]SELECT PROJECT_NUMBER,
[red]???[/red] AS ROUTE
FROM MyTable[/tt]

I can get the ROUTE with[tt]
SUBSTR(PROJECT_NUMBER, INSTR(PROJECT_NUMBER, '(') -5, [blue]3[/blue])[/tt]
but that only works if the ROUTE is [blue]3[/blue] digits long. :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

I think this should get you within striking distance - it worked with a single hard-coded value, so it should work with the full table:

SELECT project_number, LPAD(SUBSTR(project_number,strt, nd - strt +1),3,'0') AS route FROM
(SELECT project_number AS project_number, 
        INSTR(project_number,'-',-1*INSTR(project_number,'('),2)+1 strt,
        INSTR(project_number,'-',-1*INSTR(project_number,'('),1)-1 nd FROM MyTable);

Note that you could do this with a single substr, but it quickly becomes very hard to read - hence the dynamic view/subqauery approach.
You could probably also do this more cleanly with regular expressions, but I'm not familiar enough with them to venture a guess.

 

[Andrzejek]

Very interesting approach.
Thank you

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson