topic Re: How to remove multiple spaces in a single field containing unique words in Splunk Search

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

timyong80 — Tue, 29 Sep 2020 14:24:53 GMT

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.

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

richgalloway — Fri, 09 Jun 2017 12:43:39 GMT

Try this.

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

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

javiergn — Fri, 09 Jun 2017 12:43:52 GMT

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

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

somesoni2 — Fri, 09 Jun 2017 14:53:58 GMT

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

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

timyong80 — Sun, 11 Jun 2017 15:13:25 GMT

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

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

timyong80 — Sun, 11 Jun 2017 15:19:40 GMT

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

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

timyong80 — Tue, 29 Sep 2020 14:25:26 GMT

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+//"

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

timyong80 — Tue, 29 Sep 2020 14:25:32 GMT

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+//"

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

javiergn — Mon, 12 Jun 2017 08:16:47 GMT

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

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

javiergn — Mon, 12 Jun 2017 08:18:36 GMT

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"

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

richgalloway — Fri, 16 Jun 2017 19:22:19 GMT

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