Return field data where number begins 1

[mgallot]

I need to return data from an address field (address1) starting where a number exists.
Example:

Sunny Hill Gardens 1601 Sunshine Lane - data in field

1601 Sunshine Lane - data that needs to be returned

The numbers are always in different positions in the field.

Thanks for your help.

 

[r937]

ANSI SQL is going to come up empty for this

which database system are you actually running?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JarlH]

Use some views:
[tt]create view v1 (id,pos) as
select id, position('0' in addrcol) from addrtab
union
select id, position('1' in addrcol) from addrtab
...
select id, position('9' in addrcol) from addrtab[/tt]

and
[tt]create view v2 as
select id, min(pos) as pos
from v1
where pos > 0
group by id[/tt]

And finally:
[tt]select substring(addrcol from (select pos from v2 where v2.id = addrtab.id))
from addrtab[/tt]


My own test:
[tt]SQL>select addrcol,
SQL&substring(addrcol from (select pos from v2 where v2.id = addrtab.id))
SQL&from addrtab;
addrcol

========================================
Sunny Hill Gardens 1601 Sunshine Lane
1601 Sunshine Lane
===
Somewhere Else 4 Lost Place
4 Lost Place
===
40 Sutton Place
40 Sutton Place
===
Ending number 6
6
===
[/tt]

 

[r937]

jarlh, that's awesome, nice job

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JarlH]

Thank you!