How to get string from VarChar from first non-numeric value

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
dveltman1
Participant
Posts: 12
Joined: Thu Jul 02, 2015 6:12 am

How to get string from VarChar from first non-numeric value

Post by dveltman1 »

Hi, my name is Dirk and I am new here.

Hopefully we can help each other out.

My problem:
Input is house numbers like '10', '11b', '12 - c', '12/2' and I would like the output split in two output lines like this:
'10' = '10' and NULL
'11b' = '11' and 'b'
'12 - c' = '12' and '- c'
'12/2' = '12' and '/2'

So the first line gets the number untill the first occurence of a non-numeric value.
The second line gets al the characters from the first non-numeric value till the end of the string.

I have been puzzling with Alnum, Alpha and Index but I couldn't resolve.

Can anybody help me?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Since you noted this is a Parallel job, let's get you in the proper forum... done.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ShaneMuir
Premium Member
Premium Member
Posts: 508
Joined: Tue Jun 15, 2004 5:00 am
Location: London

Post by ShaneMuir »

Does it have to be a parallel job? If not you could use a server job and use the string transforms DIGITS and LETTERS
DIGITS = extracts only digits
LETTERS = extracts only characters.

Or you could use a BASIC transformer in a parallel job to do the same thing.
ShaneMuir
Premium Member
Premium Member
Posts: 508
Joined: Tue Jun 15, 2004 5:00 am
Location: London

Post by ShaneMuir »

Actually now that i think about it, Letter and Digits wouldn't work in all your examples.

For '12/2' it would return '112' for Digits and '' for letters.
ShaneMuir
Premium Member
Premium Member
Posts: 508
Joined: Tue Jun 15, 2004 5:00 am
Location: London

Post by ShaneMuir »

I am sure there is a better way but you could do the following (not sure it would work in all circumstances)

Have 3 stage variables:

Code: Select all

svConvert=Convert('1234567890','', Lnk_input.STRING)
svPart1=If svConvert ='' then Lnk_input.STRING else Lnk_input.STRING[svConvert,1,1]
svPart2=If svConvert ='' then '' else Lnk_input.STRING[svPart1,2,99]
Basically what it is doing is
svConvert: Converts all the numbers to blank
svPart1: Uses whatever is left from the input string as a delimiter, and selects the first part of the input string based on that delimiter. If there was nothing left after the numbers are converted then it outputs the input string (as it must have been all numbers)
svPart2: Uses the output of svPart1 (ie the first part of the numbers) as a delimiter, and selects the remaining parts of the input string based on that delimiter. If there was nothing left after the numbers were converted to blank it outputs blank.
dveltman1
Participant
Posts: 12
Joined: Thu Jul 02, 2015 6:12 am

Post by dveltman1 »

Thanks Shane!
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So... resolved?
-craig

"You can never have too many knives" -- Logan Nine Fingers
dveltman1
Participant
Posts: 12
Joined: Thu Jul 02, 2015 6:12 am

Post by dveltman1 »

Yes, thanks!
Post Reply