Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a column with below values:
New Haven County
Fairfield County
Los Angeles County
Atlantic County
Result should be:
New Haven
Fairfield
Los Angeles
Atlantic
Result am getting by using IF(CNTY_NM LIKE '*COUNTY*', SUBFIELD(CNTY_NM, ' ', 1))
New
Fairfield
Los
Atlantic
Thanks.
seems to work for me
LOAD *,
IF(CNTY_NM LIKE '*COUNTY*', SUBFIELD(CNTY_NM, ' County', 1)) as Result
INLINE [
CNTY_NM
New Haven County
Fairfield County
Los Angeles County
Atlantic County
];
if(right(CNTY_NM,,6)='County',left(CNTY_NM,len(CNTY_NM)-7),CNTY_NM)
IF(CNTY_NM LIKE '*COUNTY*', SUBFIELD(CNTY_NM, ' County', 1))
@Marco Wedel - The county word still appears after the county name, which I need to get rid of.
@m w - I got error in the expression but I tweaked your version to this and it seems to be working
LEFT(CNTY_NM, len(CNTY_NM)-7)
Thank you both.
How about simple Replace(CNTY_NM, ' County', '')?
Slick Marco. I would mark yours or Juraj's as Correct, but they will all work.
seems to work for me
LOAD *,
IF(CNTY_NM LIKE '*COUNTY*', SUBFIELD(CNTY_NM, ' County', 1)) as Result
INLINE [
CNTY_NM
New Haven County
Fairfield County
Los Angeles County
Atlantic County
];
@Marco Wedel - your solution works perfect. I had County as uppercase instead of lowercase on my script so I overlooked.
@ Juraj Misina - your solution works as well.
Thank you all.
You can also simplify and use Replace(CNTY_NM , ' County', '').
What about these counties: