Select - Text: Search

The Search template appears in template list when a text field of type nvarchar or varchar has been picked in the Select pane.  The template selects based on text values compared to the given value.

 

Search

Select by comparing the source field value Use option using the given Condition to the given value or values,  which can be values from a field, a specified value, or the result of an expression that evaluates to a numeric data type.

 

Use options:

 

  • number of characters - Use the number of characters in the text.
  • text - Use the text.

 

Condition options for number of characters:

 

  • between - Select records with values between the At least and At most values, inclusive.
  • equal (=) - Select records with values equal to the given value.
  • greater (>) - Select records with values greater than the given value.
  • greater or equal (>=) - Select records with values greater than or equal to the given value.
  • less (<) - Select records with values less than the given value.
  • less or equal (<=) - Select records with values less than or equal to the given value.
  • not equal (<>) - Select records with values not equal to the given value.

 

If we would like to select records that are NOT variations of the above, for example not between, select on the basis of one of the above and press Ctrl-I or Edit - Select Inverse to invert the selection.   Selecting using between and then pressing Ctrl-I is the equivalent of "not between" the given values.

 

Condition options for text:

 

  • between - Given the specified collation, falls in an alphabetic sort between the At least and At most text values.
  • contains - Contains the specified text value.
  • contains regular expression - Contains the specified regular expression.
  • ends with - Ends with the specified text value.
  • equal (=) - Is the same as the given value.
  • greater (>) - Given the specified collation, in an alphabetic sort is greater than the given value.
  • greater or equal (>=) - Given the specified collation, in an alphabetic sort is greater than or equal to the given value.
  • less (<) - Given the specified collation, in an alphabetic sort is less than the given value.
  • less or equal (<=) - Given the specified collation, in an alphabetic sort is less than or equal to the given value.
  • matches pattern (like) - Is like the pattern in the given value.  The syntax for patterns is the same as syntax for the SQL LIKE Operator, a simplified pattern matching syntax that is much easier for most people than full regular expression syntax.  This template has an Ignore case option box.
  • matches regular expression - Matches the regular expression pattern given in the value. This template has an Ignore case option box.
  • not equal (<>) - Is not the same as the given value.
  • sounds like (soundex) -  The text in the source field sounds like the given value using an English language Soundex algorithm to compare the sound of the source field value to the sound of the given value.
  • starts with - Starts with the specified text value.

 

The Trim box allows automatic trimming of whitespace characters from the source field number before comparing to the specified value:

 

  • none - Do not trim any whitespace characters (default).
  • end - Trim any whitespace from the end of the source text.
  • start - Trim any whitespace characters from the start of the source text.
  • start and end - Trim any whitespace characters from both the start and the end of the source text.

 

Launch the template by choosing a text field in the Select pane and then double-clicking the Search template.  When the template launches we can specify options.

 

 

Search : number of characters

Search based on the number of characters in the text.

Search : number of characters : between

Select records with the number of characters in the text between the At least and At most values, inclusive.

 

Search : number of characters : equal (=)

Select records with the number of characters in the text equal to the given value.

 

Search : number of characters : greater (>)

Select records with the number of characters in the text greater than the given value.

 

Search : number of characters : greater or equal (>=)

Select records with the number of characters in the text greater than or equal to the given value.

 

Search : number of characters : less (<)

Select records with the number of characters in the text less than the given value.

 

Search : number of characters : less or equal (<=)

Select records with the number of characters in the text less than or equal to the given value.

 

Search : number of characters : not equal (<>)

Select records with the number of characters in the text not equal to the given value.

Search : text

Search based on the text.

 

Search : text : between

Select records with text that falls in an alphabetic sort between the At least and At most text values, inclusive, given the specified collation.

 

We start with a table with two fields, a geometry field and a Name text field of type nvarchar.    We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose between as the Condition option.  For the At least value we enter charlie and for the At most value we enter foxtrot.  

 

We leave the Trim value set to the default none, meaning no trimming of any whitespace or other characters from the Name text strings before they are compared to the At least and the At most values.

 

We leave the Collation at default neutral, nocase, so case will not be significant.

 

For the Action, we use the default replace selection option.

 

Press Select.  

 

 

The template selects records where the Name text, using lexicographic order, falls between charlie and foxtrot, inclusive.

 

Search : text : contains

Select records with text that contains the specified text string.

 

We begin with an example table for a drawing that contains three areas, two lines and two points.  In addition to the geometry field, the table contains a text field called Name of type nvarchar.  We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose contains as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter the literal text we want to search for with the contains condition.    We enter the text ha into the Value box.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the contains comparison.    

 

We leave the Collation choice at the default neutral, nocase setting, to capture upper and lower case variations of spelling.  We could use the Collation setting to specify a language and language options if we wanted.

 

For Action, we use the default replace selection.

 

Press Select.    

 

 

The template selects the two records that contain the text sequence ha in the text in the Name field.

 

Another example, in French:   We start with a table with one text field of type nvarchar.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose contains as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter the literal text we want to search for with the contains condition.    We enter the text des.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.    We leave the Collation choice at the default neutral, nocase setting, to capture upper and lower case variations of spelling.

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records where the Name text contains the three character sequence des.  

 

Search : text : contains regular expression

Select records with text that contains the specified regular expression.

 

We start with a table with two fields, a geometry field and a Name text field of type nvarchar.    We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose contains regular expression as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter a pattern.   We enter the regular expression pattern .+a.+  

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.    We leave the Ignore case choice at the default checked setting, to capture upper and lower case variations of spelling.

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records where the Name text contains any sequence of characters that matches the regular expression pattern we specified.

 

In regular expression syntax, the dot and plus sign .+ combination matches one or more occurrences of any character.  The .+a.+ pattern used above therefore matches any string that has one or more characters followed by an a character that is then followed by one or more characters.  The result is to find all strings that contain the letter a, but not only at the very beginning or the very end of the string.

 

The records with bravo and charlie in the Name text are selected because those words have a letter a inside the word and not just at the very end or the very beginning.   The values alpha and delta are not selected because those words only have the letter a at the beginning or the end of the word.   

 

See the Regular Expressions topic for details on regular expression syntax and examples of use.

 

Search : text : ends with

Select records with text that ends with the specified text string.

 

We start with a table with one text field of type nvarchar.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose ends with as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter the literal text we want to search for with the contains condition.    We enter the text es.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.    We leave the Collation choice at the default neutral, nocase setting, to capture upper and lower case variations of spelling.

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records where the Name text ends with the two character sequence es.  

 

Search : text : equal (=)

Select records with text that is equal to the given text string.

 

We start with a table with two fields, a geometry field and a Name text field of type nvarchar.    We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose equal (=) as the Condition option.  For the Value we enter delta.

 

We leave the Trim value set to the default none, meaning no trimming of any whitespace or other characters from the Name text strings before they are compared to the At least and the At most values.

 

We leave the Collation at default neutral, nocase, so case will not be significant.

 

For the Action, we use the default replace selection option.

 

Press Select.  

 

 

The template selects records where the Name text, is exactly equal to the specified text, delta.

 

In the example above, we entered a literal text string into the Value box.   We could have, however, chosen another field or written an SQL expression for the box, as shown in the following example.

 

We start with a table with two text fields of type nvarchar.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose  equal (=) as the Condition option.   

 

From the pull down menu in the Value box we choose the Comments field.   If we wanted to compare the Name field to a fixed value, we could have chosen Value and then entered a true or false value.  Or, if we preferred to compare to the result of an SQL expression, we could have chosen Expression and then entered an SQL expression.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.    We leave the Collation choice at the default.

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records where the Name text is the same as the Comments text.

 

Note that if one or both of the two values being compared is a NULL, that is, not a determinable value, the record is not selected.

 

Search : text : greater (>)

Select records with text that is greater than the specified text string, given the specified collation.

 

Search : text : greater or equal (>=)

Select records with text that is greater than or equal to the specified text string, given the specified collation.

 

Search : text : less (<)

Select records with text that is less than the specified text string, given the specified collation.

 

Search : text : less or equal (<=)

Select records with text that is less than or equal to the specified text string, given the specified collation.
 

Search : text : matches pattern (like)

Select records with text that is like the specified pattern.

 

The syntax for patterns is syntax for the SQL LIKE Operator, a simplified pattern matching syntax that is easier for most people than full regular expression syntax.  For example, the wildcard character % matches any number of characters, including no characters.

 

This template has an Ignore case option box.

 

We start with a table that has an nvarchar text field called Name, which contains the names of cities.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

Suppose we know that entries for cities may have spelling variations.  For example, we may have reason to believe that St. Petersburg may be spelled as Saint Petersburg or using Petersberg with an e instead of Petersburg with a u.    We will use a pattern to select all such variations.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose matches pattern (like) as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter a pattern.   We enter the pattern %Peter%.  This pattern matches any text that has any sequence of zero or more characters, followed by Peter, and then followed by any sequence of zero or more characters.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.    We leave the Ignore case choice at the default checked setting, to capture upper and lower case variations of spelling.

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records that match the pattern, which will capture various spellings of St. Petersburg.

 

Another way to have accomplished the same task would be to use the contains condition, to select all text that contains Peter.

 

Another example:   We start with a table that has a geometry field for objects and a  text field called Name.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose matches pattern (like) as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter a pattern.   We enter the pattern %o.  This pattern matches any text that has any sequence of zero or more characters, with the letter o at the end.  Sine we leave the Ignore case box checked, this chooses text that ends either with lower case o or upper case O.

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records where the Name text ends in the letter o.  See the LIKE Operator topic for examples.

 

Search : text : matches regular expression

Select records with text that matches the specified regular expression pattern. This template has an Ignore case option box.

 

We start with a table with one text field of type nvarchar.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose matches regular expression as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter a pattern.   We enter the regular expression pattern .*de.*  This regular expression pattern matches any sequence of zero or more characters followed by a de and then one or more characters of any kind.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.    We leave the Ignore case choice at the default checked setting, to capture upper and lower case variations of spelling.

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records where the Name text matches the regular expression pattern we specified.

 

The regular expression pattern matches any sequence of zero or more characters followed by a de and then one or more characters of any kind.  For example, it matches any text value that has a de or a des somewhere in the text (but not a de at the very end).  The second and fourth records do not have a de anywhere so they are not selected.

 

See the Regular Expressions topic for details on regular expression syntax and examples of use.

 

Search : text : not equal (<>)

Select records with text that is not equal to the specified text string.

 

We start with a table with two text fields of type nvarchar.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose  equal (=) as the Condition option.   

 

From the pull down menu in the Value box we choose the Comments field.   If we wanted to compare the Name field to a fixed value, we could have chosen Value and then entered a true or false value.  Or, if we preferred to compare to the result of an SQL expression, we could have chosen Expression and then entered an SQL expression.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.    We leave the Collation choice at the default.

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records where the Name text is the same as the Comments text.

 

Note that if one or both of the two values being compared is a NULL, that is, not a determinable value, the record is not selected.

 

Search : text : sounds like (soundex)

Select records with text that sounds like the specified text string, using an English language Soundex algorithm to compare what text sounds like.

 

We start with a table with two fields, a geometry field and a Name text field of type nvarchar.    We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose sounds like (soundex) as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter the literal text we want to use for the Soundex algorithm comparison.   We enter the text eko.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.    

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects records where the text in the Name field sounds like eko.

 

The ch character sequence in the English word echo is pronounced like a hard k character.  The word echo is pronounced in English as though it were spelled eko and thus is selected.   Additional examples from the above table would be to use the pattern alfa to select the record for alpha, or to use the pattern charly to select the record for charlie.

 

Although the Soundex algorithm is designed for English language, it can be surprisingly useful with other languages.   Consider an example using French.

 

We start with a table with one text field of type nvarchar.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Comments field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose sounds like (soundex) as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter the literal text we want to use for the Soundex algorithm comparison.   We enter the text menhere.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.    

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records where the Soundex algorithm thinks that the Comments text when spoken aloud sounds like menhere.

 

The template's function uses a Soundex algorithm to determine if the pronunciation of two text strings in English language would be similar despite slight variations in spelling.  Although intended for English it will frequently work well enough in some other languages to be useful. This example shows a useful match even though the contents of the source field are in French language.

 

See also the discussion of the StringSoundex function in the SQL Functions topic.

 

Search : text : starts with

Select records with text that starts with the specified text string.

 

We start with a table with one text field of type nvarchar.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Name field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose starts with as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter the literal text we want to search for with the contains condition.    We enter the text Dolmens.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the equal comparison.  We leave the Collation choice at the default neutral, nocase setting, to capture upper and lower case variations of spelling.

 

For Action, we use the default replace selection.

 

Press Select.  

 

 

The template selects all records where the Name text starts with the character sequence Dolmens.  

 

 

 

See Also

Select - Text

 

Select - Text: Expression

 

Select - Text: Null

 

Select Reference

 

Select - Expression

 

Regular Expressions

 

LIKE Operator