Archive

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

Engager

Hello guys,

I'm having a bit of problem removing spaces in between several words in a column. For example, the UserName column value is John Doe. How can I combine both words together to become JohnDoe? The UserName 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.

Tags (2)
0 Karma

SplunkTrust
SplunkTrust

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):

alt text

Engager

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(UserName, ",")
| mvexpand User
Name
| eval CombinedName = User Name
| rex field = Combined
Name mod=sed "s/\s+//"

0 Karma

SplunkTrust
SplunkTrust

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"
0 Karma

Engager

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(UserName, ",")
| mvexpand User
Name
| eval CombinedName = User Name
| rex field = Combined
Name mod=sed "s/\s+//"

0 Karma

Engager

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

0 Karma

SplunkTrust
SplunkTrust

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

SplunkTrust
SplunkTrust

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

0 Karma

SplunkTrust
SplunkTrust

Try this.

... | eval Combine_Name=replace (User_Name, " ", "") | ...
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Engager

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

0 Karma

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma