Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I have following values in my field, I need to remove the special characters and unwanted text in script:
Field:
185458215 (185 abc) |
871541543# |
526542355/452153256 |
Unknown |
+39854662215 |
Expected Output:
185458215 |
871541543 |
526542355 |
Unknown 'Discard Unknown field' |
39854662215 |
Any assistance or direction would be greatly appreciated. Many thanks
Hi
Its depends on the values, logic might be differ.. For your current example, try like below
Load *, Trim(if(SubStringCount(Numbers, '/'), SubField(Numbers, '/',1), if(SubStringCount(Numbers, '('), SubField(Numbers, '(',1), KeepChar(Numbers, 0123456789)))) as Numberss Inline [
Numbers
185458215 (185 abc)
871541543#
526542355/452153256
Unknown
+39854662215
];
You can use keepchar() to retain only numbers. For cases like first value where you need to consider only the first part of the string, you can use subfield() with a separator space/ ' '.
Hi
Its depends on the values, logic might be differ.. For your current example, try like below
Load *, Trim(if(SubStringCount(Numbers, '/'), SubField(Numbers, '/',1), if(SubStringCount(Numbers, '('), SubField(Numbers, '(',1), KeepChar(Numbers, 0123456789)))) as Numberss Inline [
Numbers
185458215 (185 abc)
871541543#
526542355/452153256
Unknown
+39854662215
];
Thank you @MayilVahanan for your prompt response. It works for me. Just a question, how does the '#' character gets discarded when we have not mentioned it in the script?
It's like saying - 'Holiday (due to heat wave) for all except senior secondary'; not mentioning other students to come 🙂. Similarly, here keepchar() is telling to keep only numbers (0...9), rest would be discarded.
Makes sense. Thanks @tresesco 🙂