How to find first occurance of numeric value in a string

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
horserider
Participant
Posts: 71
Joined: Mon Jul 09, 2007 1:12 pm

How to find first occurance of numeric value in a string

Post by horserider »

I have a string VARCHAR[10]. Here is what is needed

(1) First find out if a string has any numeric digits or not.
(2) If yes, find the first position of the numeric digit.
(3) Also, find what that numeric digit is.

I can find out if a string has numeric or not using Alpha() but for those string that tells me that they do contains some numeric value, how do I find #2 and #3mentioned above?
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

Interesting requirement...

Off the top of my head:
1) Custom routine
2) Brute force using 10 index functions and some if-then-else logic
3) Get creative with length and convert functions

If this is a 1-time deal, then I'd just go the brute force route. If it will be a frequent requirement, then I'd go with the custom routine.

Mike
horserider
Participant
Posts: 71
Joined: Mon Jul 09, 2007 1:12 pm

Post by horserider »

Even if I plan to use search function (INDEX) 10 times, how to I search for numbers 0-9 in a string. Can this be done in 1 function?
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

You can do it in a Transformer stage, something like this (creative way):

Code: Select all

lnk.col[Index(Convert('1234567890', '##########', lnk.col), '#', 1), 1]
If '#' will ever exist in the input column, then use some other character that is unique instead.
Choose a job you love, and you will never have to work a day in your life. - Confucius
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

If you're prepared to use a BASIC Transformer stage or server job, you could have

Code: Select all

MatchField(InLink.TheString,"0X1N0X",2)
which would answer all three questions.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
karthi_gana
Premium Member
Premium Member
Posts: 729
Joined: Tue Apr 28, 2009 10:49 pm

Post by karthi_gana »

I am really curious to know the meaning of 0X1N0X
Karthik
karthi_gana
Premium Member
Premium Member
Posts: 729
Joined: Tue Apr 28, 2009 10:49 pm

Post by karthi_gana »

I have also seen this on

http://www.pickwiki.com/cgi-bin/wiki.pl ... AMVedit_AE

it seems like BASIC SUBROTUINE written in the above link.
Karthik
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

It's a pattern, the UniVerse equivalent of a regular expression. The MATCH operator is fully explained in the UniVerse BASIC pdf. I don't believe in posting entire chunks of manuals here, so suffice it to say it matches any number of any characters followed by one numeric character followed by any number of any characters. 'Any' can also include 'none', btw.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You can read about DataStage BASIC match patterns in, curiously, the DataStage BASIC manual.
:shock:
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

That too. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
horserider
Participant
Posts: 71
Joined: Mon Jul 09, 2007 1:12 pm

Post by horserider »

Index and Convert as proposed worked just fine.
shobhit_vk_gupta
Participant
Posts: 14
Joined: Wed May 05, 2010 1:24 am

Post by shobhit_vk_gupta »

A simple solution which I can suggest but it is in Server Job:

Let Say:

The column name in which you have to perform this logic is X.

In transformer of Server Job you can write the below mentioned transformation:

Oconv(X,'MCN')[1,1]

Above written Oconv function will give all the numerals as a output and then we are picking the first one.

Let me know if it servers the purpose.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

FYI - the DIGITS transform is a short-cut to that particular OConv function.
-craig

"You can never have too many knives" -- Logan Nine Fingers
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

The OConv will answer everything except the position , so an Index needs to be used along with it , probably in a different transformer derivation ( Assuming this is for a transformer ! )

Code: Select all

Index(InputString,Oconv(InputString,"MCN")[1,1],1)
Post Reply