How to refine my data using substring-findstring
Hi Gurus,
I have a col that has data like this.
test-name[00H]
test2-something[00H]
I only want to get
test-name
test2-something
Right now, I have SUBSTRING(col1,FINDSTRING(col1,"[",1) + 1,FINDSTRING(col1,"]",1) - 5)m but I am getting error saying
"[Derived Column [34374]] Error: An error occurred while evaluating the function."
Please help me with this.
September 11th, 2012 5:52pm
Use SUBSTRING(col1,1, FINDSTRING(col1,"[",1) )http://btsbee.wordpress.com/
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2012 5:57pm
Use SUBSTRING(col1,1, FINDSTRING(col1,"[",1) )
http://btsbee.wordpress.com/
When I use this exp my other values which are in the correct order are not showing up in that col. means it showing as blank
For the above col I see ] at the end like "test2-something["
September 11th, 2012 6:02pm
Apologies... It should be SUBSTRING(col1,1, FINDSTRING(col1,"[",1) - 1) .... if you are not always expecting your data to contain [00H] you would have to refine the expression as:
FINDSTRING(col1,"[",1) > 0 ? SUBSTRING(col1,1, FINDSTRING(col1,"[",1) - 1) : col1
http://btsbee.wordpress.com/
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2012 6:09pm
you can simply use this expression:
REPLACE(col1,"[00H]","")
http://www.rad.pasfu.com
September 11th, 2012 6:09pm