I have tried below as well but could not be able get over the above restriction,
Code:
select cast(
replace(
translate('123ABC1',repeat('#',26),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'),'#','')as integer ) as Streetnum
from sysibm.sysdummy1
I learned to know lately that there will always be a space after the street number e.g. '123 ABC' so I tried below query and it is working fine,Thanks.
Code:
SELECT
rtrim(char(substr('123 ABC1',1,LOCATE(' ','123 ABC1'))))
from sysibm.sysdummy1