- Access exclusive content
- Connect with peers
- Share your expertise
- Find support resources
03-02-2023 07:09 AM
Hello All:
Our host_names are formatted the same across our fleet. I'd like to pull out the 5-8 characters in the hostname. We've tried using trim, ltrim and rtrim, and even with them nested. Any suggestions?
In this example WX260920162Q2R
we want to pull out 0920.
Thanks!
03-02-2023 12:02 PM
Hi Brad.Herbert,
If I understand correctly you are wanting to extract these 5-8 characters from the hostname to populate a new field? If that is the case, you can use the arrayindex and regextract functions together to accomplish this. The regextract function takes a string (in this case the endpoint_name field) followed by a regex to match on the characters you are wanting to extract. In my example below, "WX(\w{5,8})" is the regex, where the function will look for WX followed by 5-8 word characters (letters or numbers), and will only capture the pattern that is contained in the parenthesis (the 5-8 word characters that follow "WX"). You could also specify a non-capturing pattern that has to match at the end of the regex as well by including it outside of the closing parenthesis and inside the closing double quotation, such as "WX(\w{5,8})YZ" if you want to capture the 5-8 word characters ONLY when they are preceded by "WX" and followed by "YZ".
The regextract function will return an array of all of the patterns that it matched on, and in this case it may be fine to use this function on its own since it is unlikely to have multiple pattern matches from a single endpoint name, but this function is commonly used in combination with the arrayindex function, which accepts an array (in this case it is the output from our regextract function) and a specified array position, and returns the corresponding value. Arrays are 0-based, meaning when we specify an array position of 0, we are telling the function to return the first value in the array.
| alter new_field = arrayindex(regextract(endpoint_name, "WX(\w{5,8})"), 0)
Regards,
Ben
03-02-2023 12:02 PM
Hi Brad.Herbert,
If I understand correctly you are wanting to extract these 5-8 characters from the hostname to populate a new field? If that is the case, you can use the arrayindex and regextract functions together to accomplish this. The regextract function takes a string (in this case the endpoint_name field) followed by a regex to match on the characters you are wanting to extract. In my example below, "WX(\w{5,8})" is the regex, where the function will look for WX followed by 5-8 word characters (letters or numbers), and will only capture the pattern that is contained in the parenthesis (the 5-8 word characters that follow "WX"). You could also specify a non-capturing pattern that has to match at the end of the regex as well by including it outside of the closing parenthesis and inside the closing double quotation, such as "WX(\w{5,8})YZ" if you want to capture the 5-8 word characters ONLY when they are preceded by "WX" and followed by "YZ".
The regextract function will return an array of all of the patterns that it matched on, and in this case it may be fine to use this function on its own since it is unlikely to have multiple pattern matches from a single endpoint name, but this function is commonly used in combination with the arrayindex function, which accepts an array (in this case it is the output from our regextract function) and a specified array position, and returns the corresponding value. Arrays are 0-based, meaning when we specify an array position of 0, we are telling the function to return the first value in the array.
| alter new_field = arrayindex(regextract(endpoint_name, "WX(\w{5,8})"), 0)
Regards,
Ben
03-02-2023 01:27 PM
Thanks Ben, we ended up going this route shortly after I posed the question. Thanks for the response!
Click Accept as Solution to acknowledge that the answer to your question has been provided.
The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!
These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!
The LIVEcommunity thanks you for your participation!