Extract substring from a string

[nasimul]

Hi,
I need to extract a substring from a string.
For example:

'FAS960C' should be converted to 'FAS960'
'FAS960HC' should be converted to 'FAS960'

Requirement: Once there is a transition from numeric to character, then we are going to stop the search and provide result till that poistion.

Please help how to do it.

Thanks,
Nasimul

[ArndW]

Nasimul,

that is an interesting question - I can't think of an easy way to do this in PX as there are no regular expression functions. The cheap way (which I wouldn't recommend) is to call a BASIC transform where this type of pattern matching can be done easily.

Can you specify the rule differently, i.e. "everything to the left of the first '9' in the string"? What is the maximum length of the string? One could use stagevars to

Code: Select all

IF INDEX(In.String,"0")THEN INDEX(In.String,"0") ELSE 9999
IF INDEX(In.String,"1")THEN INDEX(In.String,"1") ELSE 9999
IF INDEX(In.String,"2")THEN INDEX(In.String,"2") ELSE 9999
IF INDEX(In.String,"3")THEN INDEX(In.String,"3") ELSE 9999
IF INDEX(In.String,"4")THEN INDEX(In.String,"4") ELSE 9999
IF INDEX(In.String,"5")THEN INDEX(In.String,"5") ELSE 9999
IF INDEX(In.String,"6")THEN INDEX(In.String,"6") ELSE 9999
IF INDEX(In.String,"7")THEN INDEX(In.String,"7") ELSE 9999
IF INDEX(In.String,"8")THEN INDEX(In.String,"8") ELSE 9999
IF INDEX(In.String,"9")THEN INDEX(In.String,"9") ELSE 9999

FirstNumPos = MIN(MIN(MIN(Pos0,Pos1),MIN(Pos2,Pos3)),MIN(Pos4,Pos5))...

This is rather inefficient and if you have c++ skills then this would be a great place to write a buildop.

[ray.wurlod]

You could use an External Filter stage and process your string through, say, awk. This can handle regular expressions.

In BASIC you could use MatchField() functions.

[jhmckeever]

that is an interesting question - I can't think of an easy way to do this in PX as there are no regular expression functions.

Although you could add your own regular expression support using the C-source I posted <a href="viewtopic.php?t=107882"> here</a>.

J.

[ray.wurlod]

... and I'd even added that to my Favorites!

[nasimul]

Thanks for the suggestion of using External Filter stage. But a quick question: Wouldn't it be a hit on performance?

[nasimul]

Thanks for the suggestion of using External Filter stage. But a quick question: Wouldn't it be a hit on performance?

[ray.wurlod]

What do you mean by "performance" in an ETL context? Isn't being able to perform a task better than not being able to do so?