IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Function-To find only starting Numbers from a String


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sat Sep 06, 2014 2:13 am
Reply with quote

I have a column which contains Address and I need only starting number ( St Number alone ) , the expections is
e.g. input data
'123ABC'
Output
123

I have tried below query and it works fine with one restriction.

Code:
select Replace('123ABC',(Select Replace(Replace(Replace(Replace(Replace(Replace(Replace
        (Replace(Replace(Replace('123ABC','0',''),'1',''),'2',
        ''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')     
        from sysibm.sysdummy1 ) ,'') from sysibm.sysdummy1


Restriction:
If I get '123ABC1' then I do not need the last digit (1) but above query would return 1231 and I need just 123.

I know a UDF can be created for this but,I am looking for any SQL which does without a UDF.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sat Sep 06, 2014 2:37 am
Reply with quote

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
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sat Sep 06, 2014 2:56 am
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace each space in cobol string wi... COBOL Programming 2
No new posts PARSE Syntax for not fix length word ... JCL & VSAM 7
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts To find whether record count are true... DFSORT/ICETOOL 6
Search our Forums:

Back to Top