Remove spaces & special characters 5

[NewToThis2]

CV10. I have a LIC_NUMBER field that is string 20.

The field can contain alpha, nbr, dashes, slashes and/or blank spaces, for example 220-846 RN. The data in this field is various string lengths. Each entry will be different, some with spaces, some with dashes, some with slashes and some with none such as LPR8798. I need to supress all special characters and all blank spaces so that the result is one continuous string such as 220846RN. I would greatly appreciate your assistance. Thanks so much!

 

[campsys]

Create a formula using "Replace" to get rid of unwanted characters. The sample below will get rid of slashes dashes and blank spaces:


Replace (Replace (Replace (Replace({Table.LIC_NUMBER}, "/", ""), "\", ""), " ", ""), "-", "")

 

[synapsevampire]

The simplest would be a loop:

whileprintingrecords;
Stringvar MyOutput:="";
Stringvar MyInput:= {table.field};
numbervar Counter;
For Counter := 1 to len(trim(MyInput)) do(
if asc(mid(MyInput,Counter,1)) in [65 to 90]
or
asc(mid(MyInput,Counter,1)) in [48 to 57]
or
asc(mid(MyInput,Counter,1)) in [97 to 122]
then
MyOutput:=MyOutput+mid(MyInput,Counter,1)
);
MyOutput

Replace table.field with your field.

-k

 

[synapsevampire]

Don't use the replace method, that will take forever to code out all os the possibilities and you'll be writing code to exclude rather than what to include.

-k

 

[campsys]

-k

The loop works great! Much better than replace.

Thanks

 

[kskid]

Neat! I'll be adding it to my toolbag.

Have a star.

-LW

 

[NewToThis2]

Thanks everyone for your help - I can always count on this group! The loop worked great!

 

[Madawc]

A very clever solution. But there really ought to be a Crystal command that does this - collect everything that's a number, or is a standard character, since people keep needing it.

Or is it included in Crystal XI?

Madawc Williams (East Anglia, UK)