Page 1 of 1
How to get string from VarChar from first non-numeric value
Posted: Thu Jul 02, 2015 6:27 am
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?
Posted: Thu Jul 02, 2015 6:59 am
by chulett
Since you noted this is a Parallel job, let's get you in the proper forum... done.
Posted: Thu Jul 02, 2015 8:09 am
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.
Posted: Thu Jul 02, 2015 8:28 am
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.
Posted: Thu Jul 02, 2015 9:08 am
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.
Posted: Mon Jul 06, 2015 1:27 am
by dveltman1
Thanks Shane!
Posted: Mon Jul 06, 2015 6:52 am
by chulett
So... resolved?
Posted: Tue Jul 07, 2015 3:28 am
by dveltman1
Yes, thanks!