Switch to: V9V8V7V6V5

Valentina String Fields

Valentina offers 3 field types for storing text:

  • String - stores strings of the fixed length within the range of 1 to 65,535 bytes.
  • VarChar - stores strings of the variable length within the range of 1 to 4088 bytes.
  • Text - stores strings of the unlimited length (up to 2 GiB).

What string type to choose?

  • For strings with a length of 1 byte, it is better to use a Byte field.
  • For strings with a length 2..20 bytes, it is better to use a String field.
  • For strings with a length 20..1024 bytes, it is better to use VarChar or String fields.
  • For strings with a length of more than 1K bytes, it is typically more effective to use a Text field. However, if you know that the maximum length is 3 KB and the average length is 100-200 bytes, it might be better to use a VarChar type.

The string field implements fixed length strings. It always uses N bytes on the disk, even if you store an empty string.

VarChar vs String

1) A random access speed of a VarChar can be lower than the access speed of a fixed length String, because at first Valentina must locate the page of a string, then locate a string on the page.

2) Since the size of a VarChar can be several times less than the size of a String with the same maximal length, operations such as Indexing, Export, and RegEx search should be much faster for a VarChar.

3) Updating of an existing record for a VarChar is slower. This is caused by having to rearrange the whole page.

Locale Settings

Valentina kernel provides powerful support for Locale Settings. You can setup locale settings as you need for both, your database and other objects, like Tables and Fields. In this way you will be able to specify, for instance, whether your database should do case-sensitive searches or no, and so on.

Index By Words

For string fields you can specify this Index Modifier to force Valentina to build index, which contains each word as separate item. Let's look more deeply into this.

If you have 2 records in the Table, e.g.:

1:   fox jumping
2:   edit this page

then, working with regular index, we have two items in the index:

edit this page    2
fox jumping       1

Please note that such a string index does indexing of the first 255 bytes (i.e. for UTF-16, this is 127 chars).

Working with Index By Words, we get the index with five items. Each phrase is parsed on the basis of separate words, by rules of locale for this field:

edit              2
fox               1
jumping           1
page              2
this              2

Index Styles

While working with index strings, it may happen that you would need a setting A for some set of fields and a setting B for another set of fields. To simplify work with such tasks, Valentina kernel offers Index Styles. You define some style, and then you apply it to many indexes, exactly as we would do with a text in most editors.

Typical String Searches

Exact Match

Let's find records where the field `fld` matches the literal 'abcde'.

SQL:

... WHERE fld = 'abcde'
<code>
 
API:
 
<code vb>
VSet res = fld.Find( 'abcde' )

Starts With

Let's find records where the field `fld` STARTS WITH the literal 'abcde'.

SQL:

... WHERE LEFT( fld, 5 ) = 'abcde'

API:

VSet res = fld.FindStartsWith( 'abcde' )

Ends With

Let's find records where the field `fld` ENDS WITH the literal 'abcde'.

SQL:

... WHERE RIGHT( fld, 5 ) = 'abcde'

API:

VSet res = fld.FindEndsWith( 'abcde' )

Contains

Let's find records where the field `fld` CONTAINS the literal 'abcde'.

SQL:

... WHERE LOCATE( fld, 'abcde' ) > 0

API:

VSet res = fld.FindContains( 'abcde' )

LIKE

Let's find records where the field `fld` corresponds to a pattern, according to the SQL standard syntax:

  • Character '%' - means any number (0..) of any characters.
  • Character '_' - means any single character.

NOTE: if you need to use the '%' or '_' characters in your search, you have to prepend them with the backslash '\'.

SQL:

... WHERE fld LIKE '%ab_de'

API:

VSet res = fld.FindLike( '%ab_de' )

RegEx

Let's find records that correspond to a pattern according to REGEX syntax.

Valentina uses REGEX implementation of IBM ICU library. You can find description of supported REGEX syntax here http://icu.sourceforge.net/userguide/regexp.html. Hint is – ICU supports syntax close to POSIX one.

SQL:

... WHERE fld REGEX '\Aabcde\Z'

API:

VSet res = fld.FindRegEx( '\Aabcde\Z' )

A RegEx search never uses the index of a field because it needs to check all records. Search time by RegEx is proportional to the number of records in a Table.

RegEx search can be case insensitive. For this you should use the following switches:

(?i) to set case insensitive search ON for the subsequent string
(?-i) to set case insensitive search OFF for the subsequent string
... WHERE fld REGEX ‘(?i)I AM CASE INSENSITIVE(?-i)I am Case Sensitive’

NOTE: If you need to use, in your REGEX search, a character that is reserved for RegEx, you should prepend it by the backslash '\'.

...WHERE fld REGEX 'Company \(A\)'          // searches FOR "Company (A)".

Valentina.EscapeString() Usage

It is important to understand the following rule:

If you build an SQL query string using concatenation of constant strings with string(s) from user input, then you should send such a user-string into Valentina.EscapeString() method and use its result in the concatenation.

Example:

FindPersonsWithNote( inNote as String )
{
    inEscapedNote = Valentina.EscapeString( inNote )
 
    query = "SELECT * FROM Person WHERE fldNote LIKE '" + inEscapedNote + "'"
 
    curs = gDB.SqlSelect( query )
}
 
Let we call it as 
    FindPersonWithNote( "_embedded_ license'es" )
 
Query to Valentina will looks as: 
   "SELECT * FROM Person WHERE fldNote LIKE '\_embedded\_ license\'es'"