Return field data where number begins 1

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

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]

jarlh, that's awesome, nice job

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

Thank you!