Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to remove first 3 digits and hiphen(-) from the below data. Could anyone explain me how to do it. I tried many functions like index and all but not getting the desired result.
Hi,
If you need to remove the first 4 characters, you may use a function like mid():
mid('abcdefghiljk' 3) will return cdefghijk
The problem you are facing is that there is not always 4 charcaters to remove. It depends on the hyphen=> you will need to test. You will use another string function: index() that retruns an integer (0 if it does not find), FindOneOf() ....
index('abcdef' 'ef') will return 5
index('abcdef' 'x') will return 0
Now, you need to mix all that.
In the LOAD
LOAD [...]
if(index(FiledName '-')=0, FieldName, mid(Fieldname, 4)) as sth
FROM [...]
In an expression in the GUI, you could write the same as above also if your fieldname contains too many characters you want to remove.
Fabrice
Hi,
Try Mid()
=Mid('021-BioProcess', 5)
Regards,
Jagan.
Hi,
If you need to remove the first 4 characters, you may use a function like mid():
mid('abcdefghiljk' 3) will return cdefghijk
The problem you are facing is that there is not always 4 charcaters to remove. It depends on the hyphen=> you will need to test. You will use another string function: index() that retruns an integer (0 if it does not find), FindOneOf() ....
index('abcdef' 'ef') will return 5
index('abcdef' 'x') will return 0
Now, you need to mix all that.
In the LOAD
LOAD [...]
if(index(FiledName '-')=0, FieldName, mid(Fieldname, 4)) as sth
FROM [...]
In an expression in the GUI, you could write the same as above also if your fieldname contains too many characters you want to remove.
Fabrice
Hi Ayesha,
I think In the load you could try something like :
if(mid(Business,4)='-',mid(Business,5),Business) as Business2
That checks if 4 chacter is - and if it is then will trim out first 4 digits otherwise will leave as it to accommodate for the values that do not have numbers, like"Catch All" etc
A
Hi,
You can use this expression :
PurgeChar(PurgeChar(test,TextBetween(test,'','-')),'-')
Instead of test use your field.
HTH
-Shruti
Thank you everyone for all your replies..