Posted Wed, 27 Nov 2019 04:46:33 GMT by

I have a column that is a free text field.  Is there a way to use a wild card to remove a record from a report that contains specific codes?

Most of the column is fairly simple with the first 4 characters representing different location codes. IE.  10CB,30AC,10A1,20AD, After that is a free for all.  This field is 20 characters long and anyone can pretty much put anything after the first 4 characters.   I would like to be able to remove all records that begin with 10 for one report or 30 for another or even a combination of these first 4.  Just one location has many hundreds of records and i would like to be able to narrow it down by removing certain locations. 

Any help would be appreciated.




Posted Wed, 27 Nov 2019 05:29:32 GMT by

Hi Kevin!

There are a couple of ways you can accomplish what you are asking. When you are 'scanning' a character field for the occurrence of a certain group of characters, WHERE those characters appear in the field is a question to ask.

Since you have already said the first 4 characters are already the same for all records, we can use a function called substring (SUBSTR).


1. Create a new derived field

2. Right click and select Expression Editor

3. The expression would be:  SUBSTR(field, 1, 2)     <--------this means to look at your current field, start at position 1 and return 2 characters.

If you want to just grab the first 4 characters, then it would be SUBSTR(field, 1, 4)

Be sure to give this newly created field an ALT NAME. For our example we will just call it ABC.

4. If you were to display it, it would return something like 10, 30, 20, etc.

5. To eliminate those records (you are looking to eliminate ones that start with 10 and 30 in your request), in your WHERE clause you would use:

WHERE ABC NOT IN('10', '30') or you could do WHERE ABC <> '10' if you just want to do one value.


Another way to use a 'wildcard' search, when maybe the string you are looking for can appear anywhere within the field, is to use the LIKE function. In your where clause you would do WHERE field LIKE '*string*'  <------the string is what you are looking for in the field.

Please post back if you have any questions.


You must be signed in to post in this forum.