Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have one field like below it showing numbers and string values.so if requirement keep string in a field remove numbers or if requirement keep numbers remove strings.how?
data:
1203
4567
OBALT
9200
3456
22334
OEUROPE
like that.
data is field name.
Thanks
daisy
To delete the records where Data is a string:
load
*
from whatever
where num(Date) > 0
To delete the value of Data when is a string:
*,
if(num(Data) > 0,Data) as Data 1
from Whatever;
drop field Data
rename field Data1 to Data;
You can use IsNum() function to check if the field value is a number:
LOAD data
FROM ...
WHERE IsNum(data);
or for text values:
WHERE NOT IsNum(data);
T:LOAD * INLINE [
data
] where IsNum(data);
Hi,
use following expressions.
=purgechar(data,0,1,2,3,4,5,6,7,8,9) >>it Removes number from field.
=purgechar(data,'put all strings in single quote which you want to Remove') >> it Removes strings from the fields.
Regards
Anji
thanks it working.
Hi above function is working fine.
and some times my fields show another values also how to remove that.
input: output:
field Field
123 UK
111 US
UK
Us
-
Missing
it means while loading time remove null values and missing and numbers.
Yes, you can filter it if you don't require, during loading.
Please close the thread
Just combine multiple conditions in your WHERE clause using boolean operators:
WHERE
NOT IsNum(data)
AND
Len(Trim(data)) // NULL will return zero / false here
data <> 'Missing'
;
