How to remove multiple spaces in a single field containing unique words

[timyong80]

Hello guys,

I'm having a bit of problem removing spaces in between several words in a column. For example, the User_Name column value is John Doe. How can I combine both words together to become JohnDoe? The User_Name field contains various unique names with first, middle and last names (e.g. Michael Derek Adkins) and some has 2 or more spaces in between the names.

User_Name
John Doe
Thomas Hardy Jr
Liu XinWang Ken Lim

Combine_Name (desired results)
JohnDoe
ThomasHardyJr
LiuXinWangKenLim

I've tried trim function or mvjoin but it was not successful.

Thank you in advance for your kind help.

[javiergn]

I would use rex in SED mode in order to remove any space characters:

| eval Combined_Name = User_Name
| rex field=Combined_Name mode=sed "s/\s+//g"

In your example:

| makeresults | fields - _time
| eval User_Name = split("John Doe, Thomas Hardy Jr, Liu XinWang Ken Lim", ",")
| mvexpand User_Name
| eval Combined_Name = User_Name
| rex field=Combined_Name mode=sed "s/\s+//g"

Output (see picture below):

[timyong80]

Thanks a lot this seems workable. Looking at your result, it's only removing the space for the first name. There are still spaces for the subsequent names. Also, i've tried it and it only removes the first space. For example, John C Doe only returned as JohnC Doe.

Since the User_Name field will always return a lot of names, how can i not hardcode the names in split function so that it will remove the spaces for whatever names that are in the results?

Can i use the one below? It didnt worked though.

| eval split(User_Name, ",")
| mvexpand User_Name
| eval Combined_Name = User Name
| rex field = Combined_Name mod=sed "s/\s+//"

[javiergn]

You are not using eval split in the right form. Try this way instead:

| eval Combined_Name = split(User_Name, ",")
| mvexpand Combined_Name
| rex field = Combined_Name mod=sed "s/\s+//g"

[timyong80]

Thanks a lot this seems workable. Since the User_Name field will always return a lot of names, how can i not hardcode the names in split function so that it will remove the spaces for whatever names that are in the results?

Can i use the one below?

| eval split(User_Name, ",")
| mvexpand User_Name
| eval Combined_Name = User Name
| rex field = Combined_Name mod=sed "s/\s+//"

[timyong80]

Thank you. I've tried it but the column is returning blank results

[somesoni2]

You need to use global flag in your sed string "s/\s+//g"

[javiergn]

++1 for somesoni2.
Didn't notice that. Will fix my answer

[richgalloway]

Try this.

... | eval Combine_Name=replace (User_Name, " ", "") | ...

[timyong80]

Thanks Rich.
It didnt worked as the field returned blank results. Is there somethig that i missed?

[richgalloway]

Sometimes extraneous spaces affect the results. Try removing the space after replace.