String Processing (right most characters)

[Former Member]

Hello, I would like to know how to retrieve the rightmost n characters from a string ignoring trailing spaces.

Example:

if fieldx contains '012345678 '

the RightMost 5 characters would be '45678'

the Rightmost 4 characters would be '5678'

How do we accomplis this in Data Services?

Thanks.

[Former Member · Accepted solution]

Hi Sam,

You can use two string functions for achieving this. Use 'rtim_blanks' function to trim the spaces on the right side of the string and then use 'substr' function to get a substring of last 4 or 5 characters.

For example: it looks like substr(rtrim_blanks(<your string>),5,9) for extracting last 5 characters

substr(rtrim_blanks(<your string>),6,9) for extracting last 4 characters.

Hope this helps.

Regards,

Sanjay

[Former Member · Accepted solution]

What if you don't know how long the field is...It can contain 9 or 7 characters..We always want to get the right most 5 (excluding trailing blanks) characters doens't matter how long the field is....

[Former Member · Accepted solution]

Hi Sam,

Use length() function to determine the length of your string.

Regards,

Sanjay

[Former Member · Accepted solution]

HI Sanjay,

I knew about the 'length" function, but didn't know the syntax.. Well, finally figured out how to use it in Data Services..

Ended up doing this to solve the problem..

ifthenelse ((EQUIP_Qry.METER_TYPE_CD = 'S' and length(EQUIP_Qry.ATWRT) > 16),

substr(EQUIP_Qry.ATWRT,length(equip_qry.atwrt) - 15,16),

ifthenelse ((EQUIP_Qry.METER_TYPE_CD = 'S' and length(EQUIP_Qry.ATWRT) > 0),

EQUIP_Qry.ATWRT,null))

The above code worked for me for selecting the last 16 characters from this column (excluding trailing spaces)

Thanks

[former_member106536 · Accepted solution]

another way to do this is to reverse the string, take your substring, then reverse the substring back.

1. *** REVERSE STRING FUNCTION ***

$StringInput = rtrim_blanks($StringInput);

$SubstrIdx = length( $StringInput );

$HoldingVariable = '';

begin

while($SubstrIdx > 0)

begin

$HoldingVariable = $HoldingVariable || substr($StringInput,$SubstrIdx,1);

$SubstrIdx = $SubstrIdx - 1;

end

return($HoldingVariable);

end

This works.

print(JB_WordReverse( substr(JB_WordReverse( 'SOMEWORDTOTESTCAT '),1,3)));

[Former Member]

Not well documented but using a negative start position also works, -1 being the last character of the string.

print( substr( 'abcdef', -1, 1) );

Will print 'f'

print( substr( 'abcdef', -2, 1) );

Will print 'e' etc.

Specifying a negative offset > length of the string is the same as specifying a start position of 1

print( substr( 'abcdef', -7, 2) );

Will print 'ab'

[Former Member]

Thank you! exactly what I was looking for.