Return field data where number begins
Return field data where number begins
(OP)
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.
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.
Talk To Other Members
RE: Return field data where number begins
which database system are you actually running?
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Return field data where number begins
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
and
create view v2 as
select id, min(pos) as pos
from v1
where pos > 0
group by id
And finally:
select substring(addrcol from (select pos from v2 where v2.id = addrtab.id))
from addrtab
My own test:
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
===
RE: Return field data where number begins
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Return field data where number begins