Remove repeating values in a string (<> distinct values)

[#1]

Hi,

What would be the right way to remove directly repeating values in a string?

e.g.

Source string is "123444566556789"

Target string should become "12345656789".

So, it's not really finding the unique values, but rather avoiding the same number being repeated multiple times after one other.

To give some background information: each number (1-9) represents a specific status, and I need to know the number of status changes.

Thanks for your assistance!

Kind regards,

Maarten

[marcus_sommer · Accepted solution]

I think there are at least three different possible approaches. First is to use a quite large nested if-loop with checking of each char with string-functions. Second would be to split the string with a load and while len(string) <= iterno() and afterwards you applied a concat(distinct string) to put the pieces together again and the third solution would be to use How to use regular expressions.

- Marcus

[jagan]

Hi,

You can try MapSubstring

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ('map1', 'A123') returns 'A<one>23'

MapSubstring ('map1', 'baaar') returns 'bXYZar'

MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'

Replace characters with Ascii numbers in a string

Re: how to remove some part of row data?

Data Cleansing

Hope this helps you.

Regards,

Jagan.

[jagan]

Try this sample script

NumMap:

Mapping LOAD

*

INLINE [

AcutalNum, ReplaceNum

444, 4

44, 4

66, 6

55, 5];

Data:

LOAD

*,

MapSubString('NumMap', Number) AS Clean_Num

INLINE [

Number

123444566556789];

Note: you can add as many numbers as you want in mapping table.

Regards,

Jagan.

[maxgro]

my idea in the attachment

s:

load

  rowno() as id,

  str,

  replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(str, '1', '1@'), '2', '2@'), '3', '3@'), '4', '4@'), '5', '5@'), '6', '6@'), '7', '7@'), '8', '8@'), '9', '9@'), '0', '0@') as str2

inline [

str

123444566556789

123444566

1234444445555556666666444444455555555555666666666644444445555555555666666666

];

left join (s)

load

  id, AutoNumber(rowno(), id) as id2,

  SubField(left(str2, len(str2)-1), '@') as c

Resident s;

left join (s)

load

  id, id2,

  if(Previous(id)=id and Previous(c)=c, 1, 0) as flagdupl

resident s

order by id, id2;

final:

NoConcatenate load

  id, Concat(c, '', id2) as newstr

Resident s

Where not flagdupl

Group By id

Order by id, id2;

[swuehl]

You can do it like this for an unkonwn number of possible repeatitions:

REMOVE_DUPS:

LOAD ID, only(Input) as Input, Concat(SubTask, '', TaskID) as Output

WHERE SubTask <> Previous(SubTask)

GROUP BY ID;

LOAD recno() as ID,

  Input,

  mid(Input, iterno(),1) as SubTask,

  iterno() as TaskID

WHILE iterno() <= Len(Input);

LOAD * INLINE [

Input

123444566556789

];

[rubenmarin]

Hi Maarten, another way to do the task:

Sub RemoveDuplicates(String)

Let vText='';

    For i = 1 to Len(String)-1

        If Mid(String, $(i), 1)<>Mid(String, $(i)+1, 1) then

             LET vText = vText & Mid(String, $(i), 1);

        EndIf

    Next

End Sub

DataOrig:

LOAD *

Inline [

String, Other Fields

123444566556789, Other Values

123444566556789, Other Values 2

];

Data: LOAD * Inline [DumbField];

For vFieldValue=1 to FieldValueCount('String')

    LET vString = FieldValue('String', $(vFieldValue));

    CALL RemoveDuplicates('$(vString)')

    Concatenate (Data) LOAD '$(vText)' as String, [Other Fields] Resident DataOrig Where String='$(vString)';

Next

DROP Table DataOrig;

DROP Field DumbField;

[cwolf]

You can build your own macro function to do this:

function getTarget(source)
  s=""
  t=""
  for i = 1 to len(source)
    r=mid(source,i,1)
    if StrComp(r,t)<>0 then
       s=s+r
       t=r
    end if
  next
  getTarget=s
end function

In the Script:

LOAD

...

source,

getTarget(source) as target,

...

[MarcoWedel]

Hi,

a single expression solution for pure numeral strings could be:

LOAD *,    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(PurgeChar(Capitalize(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Str,'0',' a'),'1',' b'),'2',' c'),'3',' d'),'4',' e'),'5',' f'),'6',' g'),'7',' h'),'8',' i'),'9',' j'),'a a','aa'),'b b','bb'),'c c','cc'),'d d','dd'),'e e','ee'),'f f','ff'),'g g','gg'),'h h','hh'),'i i','ii'),'j j','jj'),'a a','aa'),'b b','bb'),'c c','cc'),'d d','dd'),'e e','ee'),'f f','ff'),'g g','gg'),'h h','hh'),'i i','ii'),'j j','jj')),' abcdefghij'),'A','0'),'B','1'),'C','2'),'D','3'),'E','4'),'F','5'),'G','6'),'H','7'),'I','8'),'J','9') as Str2
Inline [
Str
123444566556789
11122344456565432123456789
2345678
23343435687
244445466788
73669843889
];

hope this helps

regards

Marco

[#10]

Hello,

Thanks a lot for all the replies.

Still figuring out which one is the best for me, but all those I tested seem to be working perfectly!

The only thing I couldn't get to work is the regex proposal, but plenty of other options now

Kind regards,

Maarten