Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All!,
I have a field called Option_Code whose value is a string that I'd like to separate out.
Option_Code:
0500_0599_VALUE_PKG
9078_FENDER_LIGHTS
9188_ENGINE_BLK_HTR_110VG
0001_0249_DESTN_COUNTRY
All I want is everything from the left of the text to be deleted so that it looks like this:
VALUE_PKG
FENDER_LIGHTS
ENGINE_BLK_HTR_110VG
DESTN_COUNTRY
I'm assuming i'll need to use a trim function, but i'm not sure how the coding should be..
I tried using
=purgechar([OPTION BOX],'_')
which helps get rid of the underscores '_'
but i'd like to only have the TEXT showing ( not the beginning #'s)
Thank you for any help!
-Liz
Try something like this:
if(Left(Right(PurgeChar(Value,'0123456789'),Len(PurgeChar(Value,'0123456789'))-1),1)='_',Right(PurgeChar(Value,'0123456789'),
Len(PurgeChar(Value,'0123456789'))-2),Right(PurgeChar(Value,'0123456789'),Len(PurgeChar(Value,'0123456789'))-1)) as Final
See attached.
Try something like this:
if(Left(Right(PurgeChar(Value,'0123456789'),Len(PurgeChar(Value,'0123456789'))-1),1)='_',Right(PurgeChar(Value,'0123456789'),
Len(PurgeChar(Value,'0123456789'))-2),Right(PurgeChar(Value,'0123456789'),Len(PurgeChar(Value,'0123456789'))-1)) as Final
See attached.
Hi Liz,
there are probably several ways to do this, I think you can do it like:
TMP:
LOAD
subfield(Option_Code,'_') as SubCode,
recno() as RecNo,
rowno() as SortOrder
INLINE [
Option_Code
0500_0599_VALUE_PKG
9078_FENDER_LIGHTS
9188_ENGINE_BLK_HTR_110VG
0001_0249_DESTN_COUNTRY
];
RESULT:
LOAD concat(SubCode,'_',SortOrder) as Option_Code resident TMP where isText(SubCode) group by RecNo;
drop table TMP;
Try:
let vAlphaChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
mid(Option_Code, findoneof(Option_Code,'$(vAlphaChars)')) as YourfieldName,
Thank you for all your help!
I was able to have a separate column within the Option Code listbox, however i'd like to make a separate listbox with just the Option Box name
It would be best to create a new field in the script, using any of the suggested solutions (I would prefer mwoolf's one)
Like
let vAlphaChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
LOAD
Option_Code,
mid(Option_Code, findoneof(Option_Code,'$(vAlphaChars)')) as Option_Box,
...
from YourTable;