-
1. Re: Column with string data type, how to remove special characters?
msa s Jun 7, 2016 2:28 PM (in response to Tracey Dong) -
2. Re: Column with string data type, how to remove special characters?
Satish Kikani Jun 7, 2016 2:29 PM (in response to Tracey Dong)Try solution provided by msa s or try with IF condition.
IF FIND([Business Segment],";#") = 0
THEN [Business Segment]
ELSE LEFT([Business Segment],FIND([Business Segment],";#")-1)
END
-
3. Re: Column with string data type, how to remove special characters?
Joe OppeltJun 7, 2016 2:33 PM (in response to Tracey Dong)
REPLACE([String field], ";" , "")
that will put a null string in place of whatever is in the middle argument.
How many different special conditions are you looking to remove? To remove multiple conditions you would nest them like this:
REPLACE(REPLACE([String field], ";" , ""), ";#8", "")
Give the limited list of examples you have, maybe you could just do
MID([String Field], 1, 4)
That will just grab the first 4 chars of your string.
Does your string of special characters always start with a semicolon? If so, do a FIND() on the string and look for the semicolon. If the result is 0, then it's not in there and you just use the string as is. Else, do
MID([String field], 1, (FIND([String Field], ";")-1))
And if all else fails, there is the REGEX() function that lets you search and replace all sorts of things. (It's a whole world unto itself, so let's leave that for last if you need to go there.)
-
4. Re: Column with string data type, how to remove special characters?
Tracey Dong Jun 7, 2016 3:11 PM (in response to msa s)Thank you so much! Never used this function before
-
5. Re: Column with string data type, how to remove special characters?
Tracey Dong Jun 7, 2016 3:15 PM (in response to Satish Kikani)Also correct!
I tried the other way before posting, but it didn't work.
if FIND([Business Segment],";#")=1 then LEFT([Business Segment],FIND([Business Segment],";#")-1)
else [Business Segment]
END
Thank you so much!
-
6. Re: Column with string data type, how to remove special characters?
Joe OppeltJun 7, 2016 3:17 PM (in response to Tracey Dong)
I never saw that before either.
Thanks, MSA S !!
-
7. Re: Column with string data type, how to remove special characters?
Tracey Dong Jun 7, 2016 3:20 PM (in response to Joe Oppelt)Thank you for the information! I don't need to remove the special characters, but take out all the rest of the characters from the first special character. It was solved
Thank you so much!
-
8. Re: Column with string data type, how to remove special characters?
Satish Kikani Jun 7, 2016 3:21 PM (in response to Tracey Dong)That condition you are using is wrong.you want to cover all scenario where ";#" is present and not just when it is present at the beginning.
It should be like following.
if FIND([Business Segment],";#") >=1 then LEFT([Business Segment],FIND([Business Segment],";#")-1)
else [Business Segment]
END
or you can do following.
if FIND([Business Segment],";#") > 0 then LEFT([Business Segment],FIND([Business Segment],";#")-1)
else [Business Segment]
END
-
9. Re: Column with string data type, how to remove special characters?
Tracey Dong Jun 7, 2016 3:30 PM (in response to Satish Kikani)Great point! I totally understand now.
Thank you for the explaining!
-
10. Re: Column with string data type, how to remove special characters?
msa s Jun 7, 2016 5:22 PM (in response to Joe Oppelt)Thankyou Joe for the answer and detailed explanation