Transformation logic - need to grab substring

[hemanthakumar]

Hi,
i have following requirement.
If the value contains "-" then use the last three characters of the string else use the whole string.

ex: 1) customer_usa output is usa
2) india output is india.
Any one can help me to resolve for the same.

[chulett]

Index() would find the position of the "-" and then you conditionally substring off everthing after it. Or not.

[HariK]

Assuming only one instance of '_' occurs and you need only last three characters.

If index(column_name,'_',1) > 0 Then right(column_name,3)
Else column_name

[hemanthakumar]

i applied the logic, but its giving wrong result.
Thanks for your response.

[hemanthakumar]

Hi,
i have following requirement.
If the value contains "-" then use the last three characters of the string else use the whole string.

ex: 1) customer-usa output is usa ( corrected the delimiter)
2) india output is india.
Any one can help me to resolve for the same.

[HariK]

All you have to do is to read posts by Craig or me to get the logic.

[hemanthakumar]

i used dcount and field functions. Its working fine :D

[chulett]

i applied the logic, but its giving wrong result.

You'd have to show us how you "applied" it and what results you saw as it should have worked just fine. And, like most things, there are multiple solutions to most problems - dcount and field being one such example.

[arunkumarmm]

Or you can try

Code: Select all

 If Count(Column,'_') > 0 Then Column[3] Else Column

[hemanthakumar]

Thanks Arun, " If Count(Column,'-') > 0 Then Column[3] Else Column" is also working fine.

[chulett]

As would the Index() and Right() functions.

[agpt]

Or you can try

Code: Select all

 If Count(Column,'_') > 0 Then Column[3] Else Column

Hi Arun,

Can you please tell me what Column[3] does here exactly?

[agpt]

i used dcount and field functions. Its working fine :D

Hemanth,

Can you please give the exact syntax you used?

[chulett]

Can you please tell me what Column[3] does here exactly?

Without the starting position, it starts from the end so that means to substring off the last 3 characters from the string.

[agpt]

Can you please tell me what Column[3] does here exactly?

Without the starting position, it starts from the end so that means to substring off the last 3 characters from t ...

Hi Chulett... Sorry I couldn't read your whole message but I think I got the answer to my question. Thanks a lot!!!!