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
[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