how to user wildcard characters with where cause to remove some records.

[lalitkgehlot89]

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

[arulsettu]

from table

where not wildmatch('*IB00*')

[PrashantSangle]

Hi,

from table

where not wildmatch('IB00*')

* before IB00 not necessary.

Regards,

[sunny_talwar]

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

[lalitkgehlot89]

Thanks Sunny,

But I want to remove multiple records,

like IB00,ID00,AA00,ib00,Ab00

in this case what should i do.

[PrashantSangle]

Hi,

not wildmatch(FieldName,' IB00*','ID00*','AA00*','ib00*','Ab00*')

Regards,

[sunny_talwar]

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