Page 1 of 2
Transformation logic - need to grab substring
Posted: Tue Aug 31, 2010 6:33 am
by 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.
Posted: Tue Aug 31, 2010 6:38 am
by chulett
Index() would find the position of the "-" and then you conditionally substring off everthing after it. Or not.
Re: Transormation logic
Posted: Tue Aug 31, 2010 6:41 am
by 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
Posted: Tue Aug 31, 2010 6:48 am
by hemanthakumar
i applied the logic, but its giving wrong result.
Thanks for your response.
Posted: Tue Aug 31, 2010 6:53 am
by 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.
Posted: Tue Aug 31, 2010 7:02 am
by HariK
All you have to do is to read posts by Craig or me to get the logic.
Posted: Tue Aug 31, 2010 7:05 am
by hemanthakumar
i used dcount and field functions. Its working fine :D
Posted: Tue Aug 31, 2010 7:16 am
by chulett
hemanthakumar wrote: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.
Posted: Tue Aug 31, 2010 9:34 am
by arunkumarmm
Or you can try
Code: Select all
If Count(Column,'_') > 0 Then Column[3] Else Column
Posted: Tue Aug 31, 2010 11:08 pm
by hemanthakumar
Thanks Arun, " If Count(Column,'-') > 0 Then Column[3] Else Column" is also working fine.
Posted: Wed Sep 01, 2010 6:56 am
by chulett
As would the Index() and Right() functions.
Posted: Thu Sep 02, 2010 11:46 am
by agpt
arunkumarmm wrote: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?
Posted: Thu Sep 02, 2010 11:49 am
by agpt
hemanthakumar wrote:i used dcount and field functions. Its working fine :D
Hemanth,
Can you please give the exact syntax you used?
Posted: Thu Sep 02, 2010 3:42 pm
by chulett
agpt wrote: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.
Posted: Thu Sep 02, 2010 10:02 pm
by agpt
chulett wrote:agpt wrote: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!!!!