INFO: Two Ways to Search & Remove Characters from a Text String (120098)

MORE INFORMATION

Method One: Use SUBSTR() and ATC() Functions

You can use the ATC() and SUBSTR() functions to eliminate unwanted characters from database fields and memory variables.



For example, look at the following code:

   a="123.45"
   y=SUBSTR(a,1,ATC(".",a)-1)+SUBSTR(a,ATC(".",a)+1)
   b="123 45"
   y=SUBSTR(b,1,ATC(" ",b)-1)+SUBSTR(b,ATC(" ",b)+1)
				

The variables a and b contain the text strings "123.45" and "123 45" respectively. The code removes the period (.) and the blank space from the variables.

The SUBSTR() function parses the variable into separate pieces based on the value returned by the ATC() function. The ATC() function searches for a string in a variable or field and returns the numeric position of the first character in the string if it is found. If the ATC() function can't locate the string, it returns zero (0).

By combining the SUBSTR() and ATC() functions, you can construct complex statements to alter data in numerous ways.

Method Two: Use STRTRAN() Function

You can use the STRTRAN() function to search and replace characters within a text string using a single command. The following example code shows you how to use STRTRAN() to remove an unwanted period (.) from a text string:

   x="123.4567"
   y=strtran(x,".","")
   ? y  && The variable y now contains "1234567" as a text string.
				

By default, STRTRAN() looks for all occurrences of the second parameter listed in the function. By adding a numeric value in the third parameter, you can force the function to ignore earlier instances of a character. By adding a fourth numeric parameter, you can control the number of occurrences the function replaces.

The following example code shows you how to remove the second period from a text string while preserving the first occurrence:

   x="123.4567.8910"
   y=strtran(x,".","",2)
   ? y
				

The variable y returns "123.45678910" as a text string.