TI String Replace Function

[macky29]

Hi guys,

Is there a String Replace Function in TI?

I'm encountering a problem when I Write Back to an Oracle Table because some string value that I need to write has single qoute(') character. I plan to replace it with double qoute ('') during run time so that the Oracle Table will accept it.

Tnx,
Macky

[Alan Kirk]

Hi guys,
Is there a String Replace Function in TI?
I'm encountering a problem when I Write Back to an Oracle Table because some string value that I need to write has single qoute(') character. I plan to replace it with double qoute ('') during run time so that the Oracle Table will accept it.

Short answer no (though it's an odd oversight), longer answer I wouldn't use it with quotes anyway since those can be mind bending. Whenever I have to use quote characters as literal strings I always use the Char() function.

You can improvise a replace function by using the Scan() and Subst() functions; basically, every time you find the character you're looking for, take the part of the string before it (if any), whack in a double quote character, then add on the bit of the string after it. This tested out OK:

Code: Select all

s_StringWithQuotes  = char(39) | 'This' | char(39) | 'is'  | char(39) | 'such' | char(39) | 'a'  | char(39) | 'string' ;

AsciiOutput ( 'F:\Temp\SWSQ.txt', s_StringWithQuotes );

l_LocNext = Scan ( Char(39), s_StringWithQuotes  );

While ( l_LocNext > 0 );

    s_StringWithQuotes  = Subst ( s_StringWithQuotes , 1, l_LocNext - 1) | Char(34) | Subst ( s_StringWithQuotes , l_LocNext+1, Long (s_StringWithQuotes ) - l_LocNext );

     l_LocNext = Scan ( Char(39), s_StringWithQuotes  );

End;

AsciiOutput ( 'F:\Temp\SWDQ.txt', s_StringWithQuotes );

[David Usherwood]

Assuming you are communicating directly to Oracle via ODBCOUTPUT, you could use the Oracle PL/SQL Translate function in the SQL string you are sending.

[macky29]

Tnx guys, I already considered the solutions you gave. just hoping to make my life easier if I can do it with a TI Function.

[Alan Kirk]

Tnx guys, I already considered the solutions you gave. just hoping to make my life easier if I can do it with a TI Function.

Ah. Clearly you haven't seen the sign over the doorway leading to the TI development team's office: "A little (sometimes a lot) of suffering is good for the soul".

Were it otherwise we'd have the type of function that you're after, find and replace in the editor, debugging tools, For loops, function names which are consistent across all aspects of the interface, ItemSkip and ItemReject would throw a compilation error if used in the wrong tabs, chore scheduling would be in local time... oh my is that the time? I could spend all night typing these...

[Mems]

Thanks gents... Not sure were I would be without this

[macsir]

Just want to quickly share my code, sample code for replacing single quote with space

Code: Select all

vTemp = '';
vChar = '';
k=1;
WHILE( k <= LONG(vOriginalString) );
vChar = SUBST(vOriginalString, k, 1);
  IF(vChar @= '''');
    vChar = ' ';
  ENDIF;
vTemp = vTemp | vChar;
k = k + 1;

END;
vOriginalString = vTemp;

[pandinus]

For some more elaborate and dynamic find/replace functions in TI, I was forced to do some cool stuff a while ago: regular expressions.

While not being exactly regular expressions as per definition, they offer a lot of flexibility and have become my golden hammer in many situations.

It works like this:

• *Define your original string
  *Define your filter expression
  *Define any replacement rules.

Example 1:
Here we do a simple replace. Replace any space characters with underscores.

Code: Select all

vString = 'TM1 is great!';
vExpression = ' ';
vReplacement = '_';

Becomes

Code: Select all

TM1_is_great!

Example 2:
Here we do not only want to replace, we want to add to a matched string. For this we use the $& to refer to the matched string.

Code: Select all

vString = 'CellPutN(vValue, vCubename, cm_dim1, cm_dim2, cm_dim3)';
vExpression = 'CellPut?(*,*,';
vReplacement = '$& cm_extra, ';

Becomes

Code: Select all

CellPutN(vValue, vCubename, cm_extra, cm_dim1, cm_dim2, cm_dim3)

Example 3:
When not replacing stuff, sometimes we just want just look for stuff in strings. This will give you the FIRST occurrence of the match based on the expression in the string, and the length of the matched string.

Code: Select all

vString = 'TM1 is relatively ok!';
vExpression = 'el';

Becomes

Code: Select all

"TM1 is relatively ok!","el","9","2"

When using wildcards:

Code: Select all

vString = 'TM1 is relatively ok!';
vExpression = '1*!';

Becomes

Code: Select all

"TM1 is relatively ok!","1*!","3","19"

I have attached the required .pro files to this post for your entertainment.
You run the Regex_test process for testing.
The logic is in the Sys_regex_replace and Sys_regex_find processes.

[Wim Gielis]

Seems to me that the post above is a very good candidate for the "TM1 Useful code, tips and tricks" subforum.

[lotsaram]

Seems to me that the post above is a very good candidate for the "TM1 Useful code, tips and tricks" subforum.

I agree. Bookmarked