Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to remove some rows from my date.
please let me know how to use wildcard character with where clause.
in my data records which i want to remove starts with IB00
Please help me out asap.
Regards,
Lalit Kumar
from table
where not wildmatch('*IB00*')
Hi,
from table
where not wildmatch('IB00*')
* before IB00 not necessary.
Regards,
You can also try:
FROM SourceTable...
Where FieldName NOT LIKE IB00*;
or
FROM SourceTable...
Where not WildMatch(FieldName, 'IB00*');
or
FROM SourceTable...
Where Left(FieldName, 4) <> 'IB00';
I would prefer NOT LIKE over NOT WildMatch, because WildMatch will remove the null in the FieldName together with any FieldName IB00*, whereas NOT LIKE will only remove those rows where fieldname starts with IB00, while still keeping null
Thanks Sunny,
But I want to remove multiple records,
like IB00,ID00,AA00,ib00,Ab00
in this case what should i do.
Hi,
not wildmatch(FieldName,' IB00*','ID00*','AA00*','ib00*','Ab00*')
Regards,
If I may provide an easier alternative, I would suggest creating a mapping table.
MappingTable:
Mapping
LOAD * INLINE [
FieldName, Flag
IB00, 1
ID00, 1
AA00, 1
ib00, 1
Ab00, 1
];
Fact:
LOAD *
Where Flag = 0;
LOAD *,
ApplyMap('MappingTable', Left(FieldName, 4), 0) as Flag
From Source;
This might be easier to maintain in the long run.
HTH
Best,
Sunny