Switch to: V12V11V10V9V8V7V6V5

String Functions

For string functions, the position starts with 1.

CHR( code )

Returns symbol for code.

SELECT CHR(65);
=> 'A'

CONCAT( str1, str2, ... )

Returns a string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have one or more arguments. A numeric argument is converted to its equivalent string form.

SELECT CONCAT('Va', 'len', 'tina');
=> 'Valentina'
SELECT CONCAT('Va', NULL, 'na');
=> NULL
SELECT CONCAT(14.3);
=> '14.3'

CONVERT_ENUM_VALUE_TO( enum, value [, locale] )

NEW in 5.0

Returns a string that corresponds to the given English value of enum type for the specified locale. If the third parameter skipped then the current locale is used. You can use the current locale using “SET PROPERTY Language TO 'LocaleName';” command. Actually in most cases you should use the current locale to get more flexible code.

If the specified locale name is not found, then the same English value is returned.

This function should be used mostly with SELECT command, to extract from a database ENUM values in the localized form.

This function can also accept a numeric value to convert it into the corresponded value of the specified locale.

ERRORS:

  • returns error if the specified enum type is not found.
SELECT convert_enum_value_to( 'DayOfWeek', f1, 'de' ) FROM T1;

CONVERT_ENUM_VALUE_FROM( enum, value [, locale] )

NEW in 5.0

Returns an English string of enum type that corresponds to the given value of the specified locale. If the third parameter skipped then the current locale is used. You can use the current locale using “SET PROPERTY Language TO 'LocaleName';” command. If the third parameter skipped then the current locale is used. You can use the current locale using “SET LOCALE TO 'LocaleName'” command. Actually in most cases you should use the current locale to get more flexible code.

This function should be used mostly with INSERT/UPDATE commands, to insert ENUM values in the localized form which usually comes from a user input.

This function can also accept a numeric value to convert it into corresponded English value. In this case third parameter is not required.

ERRORS:

  • returns error if the specified enum type is not found.
  • returns error if value cannot be converted, e.g. value is not found in the locale, or locale name itself is not found.
INSERT INTO T1(f1) VALUES ( convert_enum_value_from( 'DayOfWeek', 'Montag', 'de' ) );
   --> 'Monday'
INSERT INTO T1(f1) VALUES ( convert_enum_value_from( 'DayOfWeek', 1 ) );
   --> 'Monday'

DATABASEVERSION()

Returns the current version of database file format.

SELECT DATABASEVERSION();
=> 592

DECODE( str, password )

Returns decoded with Password Str string using BlowFish algorithm.

SELECT DECODE( ENCODE( 'Test1234567890', 'pwd'), 'pwd')
=> Test1234567890

ENCODE( str, password )

Returns encoded with Password Str string using BlowFish algorithm.

SELECT DECODE( ENCODE( 'Test1234567890', 'pwd'), 'pwd')
=> Test1234567890

FORMAT

FORMAT( VALUE [,pattern] [,format_type] [,locale] )

Returns formatted string for any value.

Pattern

Symbol Location Meaning
0 Number Digit
1-9 Number '1' through '9' indicate rounding.
@ Number Significant digit
# Number Digit, zero shows as absent
. Number Decimal separator or monetary decimal separator
- Number Minus sign
, Number Grouping separator
E Number Separates mantissa and exponent in scientific notation. Need not be quoted in prefix or suffix.
+ Exponent Prefix positive exponents with localized plus sign. Need not be quoted in prefix or suffix.
; Subpattern boundary Separates positive and negative subpatterns
% Prefix or suffix Multiply by 100 and show as percentage
\u2030 Prefix or suffix Multiply by 1000 and show as per mille
¤ (\u00A4) Prefix or suffix Currency sign, replaced by currency symbol. If doubled, replaced by international currency symbol. If tripled, replaced by currency plural names, for example, “US dollar” or “US dollars” for America. If present in a pattern, the monetary decimal separator is used instead of the decimal separator.
' Prefix or suffix Used to quote special characters in a prefix or suffix, for example, “'#'#” formats 123 to “#123”. To create a single quote itself, use two in a row.
* Prefix or suffix boundary Pad escape, precedes pad character

A pattern may contain a positive and negative subpattern, for example, “#,##0.00;(#,##0.00)”. Each subpattern has a prefix, a numeric part, and a suffix. If there is no explicit negative subpattern, the negative subpattern is the localized minus sign prefixed to the positive subpattern. That is, “0.00” alone is equivalent to “0.00;-0.00”. If there is an explicit negative subpattern, it serves only to specify the negative prefix and suffix; the number of digits, minimal digits, and other characteristics are ignored in the negative subpattern. That means that “#,##0.0#;(#)” has precisely the same result as “#,##0.0#;(#,##0.0#)”.

The grouping separator is a character that separates clusters of integer digits to make large numbers more legible. It commonly used for thousands, but in some locales it separates ten-thousands. The grouping size is the number of digits between the grouping separators, such as 3 for “100,000,000” or 4 for “1 0000 0000”. There are actually two different grouping sizes: One used for the least significant integer digits, the primary grouping size, and one used for all others, the secondary grouping size. In most locales these are the same, but sometimes they are different. For example, if the primary grouping interval is 3, and the secondary is 2, then this corresponds to the pattern “#,##,##0”, and the number 123456789 is formatted as “12,34,56,789”. If a pattern contains multiple grouping separators, the interval between the last one and the end of the integer defines the primary grouping size, and the interval between the last two defines the secondary grouping size. All others are ignored, so “#,##,###,####” == “###,###,####” == “##,#,###,####”.

Datetime patterns

Symbol Meaning Example Result
G era designator G AD
y year yy 96
yyyy or y 1996
Y year of “Week of Year” Y 1997
u extended year u 4601
Q quarter Q or QQ 02
QQQ Q2
QQQQ 2nd quarter
q Stand Alone quarter q or qq 02
qqq Q2
qqqq 2nd quarter
M month in year M or MM 09
MMM Sept
MMMM September
MMMMM S
L Stand Alone month in year L or LL 09
LLL Sept
LLLL September
LLLLL S
w week of year w or ww 27
W week of month W 2
d day in month d 2
dd 02
D day of year D 189
F day of week in month 2 (2nd Wed in July)
g modified julian day g 2451334
E day of week E, EE or EEE Tues
EEEE Tuesday
EEEEE T
e local day of week e or ee 2
example: if Monday is 1st day, Tuesday is 2nd ) eee Tues
eeee Tuesday
eeeee T
c Stand Alone local day of week
a am/pm marker a pm
h hour in am/pm (1~12) h 7
hh 07
H hour in day (0~23) H 0
HH 00
k hour in day (1~24) k 24
kk 24
K hour in am/pm (0~11) K 0
KK 00
m minute in hour m 4
mm 04
s second in minute s 5
ss 05
S millisecond rounds to count of letters) S 2
SS 24
SSS 235
SSSS 2350
A milliseconds in day A 61201235
z Time Zone: specific non-location z, zz, or zzz PDT
zzzzz Pacific Daylight Time
Z Time Zone: RFC 822 Z, ZZ, or ZZZ -0800
Time Zone: localized GMT ZZZZ GMT-08:00
v Time Zone: generic non-location v PT
vvvv Pacific Time or United States (Los Angeles)
V Time Zone: generic non-location V PT
VVVV United States (Los Angeles)
W week in month 2

Format type

Valentina support following formatting types:

Name Description Pattern support
kNumberFormat Default format type (normal number format) Yes. Example:'###.##'
kCurrencyFormat Formats a value as currency No
kPercentageFormat Formats a value as percents (the value is multiplied by 100 before being displayed) No
kScientificFormat Formats a value in scientific notation No
kRuleBasedFormat_SpellOut Spells out a value in words No
kRuleBasedFormat_Ordinal Attaches an ordinal suffix to the end of a number (e.g. “123rd”) No
kRuleBasedFormat_Duration Formats a duration in seconds as hours, minutes, and seconds No
kRuleBasedFormat_Pattern Rule-based format defined by pattern Yes. See details: ICU rule-based patterns
kDateFormatFull It is pretty completely specified, such as 'Tuesday, April 12, 1952 AD' for date or '11:12:13 AM Eastern European Summer Time' for time No
kDateFormatLong Such as 'January 12, 1952' for date or '11:12:13 AM GMT+03:00' for time No
kDateFormatMedium Such as 'Jan 12, 1952' for date or '3:30:32pm' for time No
kDateFormatShort Completely numeric, such as '12/13/52' for date or '3:30pm' No
kDateFormat_Pattern Datetime pattern based format Yes. Example:'YYYY-MM-dd hh:mm:ss'

Locale

You may set desirable location or skip this param (current location will be applied)

Examples

SELECT FORMAT(123456.123456);
=> FOR US locale: '123,456.123'
SELECT FORMAT(123456.123456, '###.##');
=> '123456.12'
SELECT FORMAT(123456.123456, '', 'kCurrencyFormat', 'en_US')
=> '$123,456.12'
SELECT FORMAT(0.123456, '', 'kPercentageFormat' )
=> '12%'
SELECT FORMAT(0.123456, '##.##*%', 'kNumberFormat' )
=> '0.12%'
SELECT FORMAT(0.123456, '', 'kScientificFormat' )
=> '1.23456E-1'
SELECT FORMAT(123456.123456, '', 'kRuleBasedFormat_SpellOut', 'US_us')
=> 'one hundred and twenty-three thousand, four hundred and fifty-six point one two three four five six'
SELECT FORMAT(123456.123456, '', 'kRuleBasedFormat_Ordinal', 'US_us')
=> '123,456th'
SELECT FORMAT(3600, '', 'kRuleBasedFormat_Duration')
=> '1:00:00'
SELECT FORMAT(now(), '', 'kDateFormatFull')
=> 'Wednesday, June 17, 2009 10:43:45 AM GMT+03:00'
SELECT FORMAT(now(), '', 'kDateFormatLong')
=> 'June 17, 2009 10:44:44 AM GMT+03:00'
SELECT FORMAT(now(), '', 'kDateFormatMedium')
=> 'Jun 17, 2009 10:45:23 AM'
SELECT FORMAT(now(), '', 'kDateFormatShort')
=> '6/17/09 10:45 AM'
SELECT FORMAT(now(), 'YYYY-MM-dd hh:mm:ss', 'kDateFormat_Pattern')
=> '2009-06-17 10:47:12'

See also: ICU User Guide

FROMBIN( str )

NEW for v.5.0

Converts the binary data into bit-string in the form '011010100'.

Can be used to convert data stored in the FixedBinary or VarBinary field into string of bit values.

SELECT FromBin( 'ABC' )     // A=65, B=66, C=67
=> '010000010100001001000011'

See also: ToBin() function.

HEAD( str, charSet )

HEAD( str, NOT(charSet) )

Returns the left substring of a Str that contains only characters from a CharSet. HEAD() allows using of operator NOT for Charset.

SELECT HEAD('aesop', 'ea') 
=> 'ae'
SELECT HEAD('aesop', NOT('spo') ) 
=> 'ae'

See also: STRSPN(), STRCSPN(), TAIL()

HEX( number )

Returns string representation of hex number.

SELECT HEX( 327680 ) 
=> ‘0x50000’

INITCAP( str )

Returns the string Str with the first character in Upper case.

SELECT INITCAP ( 'valentina' )
=> 'Valentina'

INSERT( str, pos, len, newstr )

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.

SELECT INSERT('Valentina', 6, 4, 'base' );
=> 'Valenbase'
SELECT INSERT('Valentina', 2, 3, 'fast')	
=> 'Vfasntina'
 
SELECT INSERT('Valentina', 20, 3, 'fast')	
=> 'Valentina'
SELECT INSERT('Valentina', -1, 3, 'fast')	
=> 'Valentina'

LEFT( str, len )

Returns the leftmost len characters from the string str.

SELECT LEFT('database', 5);
=> 'datab'

NOTE: If the first argument of the LEFT() function is an indexed string field, then Valentina will use its index to find result.

LENGTH( str )

See description here.

LIKE Operator

expr LIKE pat [ESCAPE escape_character]

Operator LIKE compares the string produced by expression EXPR with the pattern pat using simple regular expressions of SQL. Returns TRUE(1) or FALSE(0). Operator LIKE allows usage of the following pattern symbols in the pattern pat:

Character Description
% Matches to any number of characters, even zero.
_ Macthes exactly to one character.
SELECT IF('Hello!' LIKE 'Hello_', 1, 0);
=> 1
SELECT IF('Hello!' LIKE '%H%o%', 1, 0);
=> 1

If you have a search string which contains a pattern character, then you should escape it. By default the escape character is ‘\‘.

String Description
\% means one character ‘%’
\_ means one character ‘_’
SELECT IF('Hello!' LIKE 'Hello/_' ESCAPE '/', 1, 0);
=> 0
SELECT IF('Hello_' LIKE 'Hello/_' ESCAPE '/', 1, 0);
=> 1

Operarator LIKE also allows numeric expressions:

SELECT IF(10 LIKE '1%', 1, 0);
=> 1

expr NOT LIKE pat [ESCAPE 'escape-char']

Equivalent to NOT (expr LIKE pat [ESCAPE 'escape-char']).

SELECT IF(10 NOT LIKE '1%', 1, 0);
=> 0

NOTE If we have form as WHERE fld LIKE '%WordStart', then Valentina may use index of the field if it exists.

LOCATE( substr, str [,pos] )

Returns the position of the first occurrence of substring substr in string str. If the parameter pos is given, then search starts from the specified position. Returns 0 if substr is not in str.

SELECT LOCATE('len', 'Valentina');
=> 3
SELECT LOCATE('xlen', 'Valentina');
=> 0

LPAD( str, len, padstr )

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

SELECT LPAD('hello',7,'??');
=> '??hello'

LOWER( str )

Returns the string str with all characters changed to lower case.

Note: If str is a field then its current settings affect this operation. If str is a string constant then settings are used of the database for which query is executed.

SELECT LOWER('FUNCTION');
=> 'function'

LTRIM( str [, charSet] )

Returns the string str with left space and, optionally CharSet, characters removed.

SELECT LTRIM( '    FUNCTION' );
=> 'FUNCTION'
 
SELECT LTRIM( '12345    FUNCTION', '123456789' );
=> 'FUNCTION'

MD5( str )

[NEW in v4.6]

Calculates an MD5 128-bit checksum for the string. The value is returned as a binary string of 32 hex digits, or NULL if the argument was NULL. The return value can be used, for example, as a hash key of long text. If you going to save this value to a Table Field, then the best choice is String[32].

SELECT MD5('1234567890')
=> E807F1FCF82D132F9BB018CA6738A19F

Normalize( inSourceStr )

[NEW in 4.9.1]

Normalize a unicode string. The string will be normalized according the specified normalization mode and options.

UPDATE T
    SET fld = Normalize(fld)
WHERE IsNormalized(fld) = FALSE;

See also:

RAND_REGEXP( PatternStr [, IndexValue] )

NEW in 5.5.8

Returns the random string following regex PatternStr.

If IndexValue parameter is not specified, then the internal rand() is used to generate a random string value. You can use IndexValue parameter with values 1..N to get same sequence of generated strings. This is useful e.g. to generate data for benches, but not for tests.

String Description
() Groups the operators that are inside the parentheses.
{n} Generates n instances of the previous item. For example, a{2} generates 'aa'.
{n,m} Generates at least n instances but no more than m instances of the previous item. For example, a{2,4} generates 'aa', 'aaa', or 'aaaa'.
| Generates an item on either side of the | character.
[aeiou] Generates any character inside the brackets.
[a-z] Generates any character in the specified range of characters.

Pattern Examples:

Pattern Description
(F | M) A simple representation of gender.
[1-9][0-9]{2,2}-[1-9][0-9]{2,2}-[0-9]{4,4} A representation of phone number. Like 800-555-8446
[1-9][0-9]{4}-[0-9]{4} ZIP code. Like 98008-2405
[1-6]{1}[0-9]{1,3} (SE|NE|NW|SW) [1-2]{1}[0-9]{1,2}th (ST|CT|PL|AVE), (Redmond, WA 9805[0-9]|Bellevue, WA 9800[1-9]|Sammamish, WA 9807[0-9]|Seattle, WA 9806[0-9]|Issaquah, WA 9808[0-9]) Postal address. Like “423 SE 21th PL, Sammamish, WA 98071”
Seattle|(New York)|Boston|Miami|Beijing|(Los Angles)|London|Paris City list.
[a-z]{5,8}@(hotmail\.com|msn\.com|[a-z]{3,8}\.(com|net|org)) E-mail address.
[1-9][0-9]{3} [0-9]{4} [0-9]{4} [0-9]{4} Credit card number.

RAWTOHEX( str )

Returns the string representing the string str in the HEX form.

SELECT RAWTOHEX ('data')
=> 64617461

REGEX Operator

expr REGEX pattern_exp [ESCAPE ‘escape-char’]

Valentina uses IBM ICU library implementation of REGEX. You can find description of supported syntax here: http://icu.sourceforge.net/userguide/regexp.html

Operator REGEX works in the way similar to operator LIKE of SQL. Note, that this is not a function, so we do not use (). Instead we put two expressions on left and right sides of the REGEX operator.

REGEX operator applies

SELECT IF('abcdef' REGEX 'bc.*f', 1, 0);
=> 1

Note, you cannot use () feature of REGEX to store groups of characters and later reffer them by \n syntax. For this you should use regex_replace() function added in v4.5.1 of Valentina.

REGEX_REPLACE()

[NEW in 4.5.1]

expr REGEX_REPLACE( OriginalStr, PatternStr, ReplaceStr )

Valentina uses IBM ICU library implementation of REGEX. You can find description of supported syntax here: http://icu.sourceforge.net/userguide/regexp.html.

SELECT REGEX_REPLACE('abcd' '(ab)(cd)', '$2--$1:' );
=> 'cd--ab'

REPEAT( str, count )

Returns string that have string str repeated count times. If count < 0 returns empty string. Returns NULL if str or count is NULL.

SELECT REPEAT('Valentina', 2);
=> 'ValentinaValentina'

REPLACE( str, from_str, to_str )

Returns the string str with all occurrences of the string from_str replaced by the string to_str.

SELECT REPLACE('paradise', 'a', 'x');
=> 'pxrxdise'

REVERSE( str )

Returns the string str with the order of characters reversed.

SELECT REVERSE('data');
=> 'atad'

RIGHT( str, len )

Returns the rightmost len characters from the string str.

SELECT RIGHT('database', 4);
=> 'base'

RPAD( str, len, padstr )

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

SELECT RPAD('hello',7,'?');
=> 'hello??'

RTRIM( str [, charSet] )

Returns the string str with right space and, optionally charSet, characters removed.

SELECT RTRIM( 'data  ' );
=> 'data'
 
SELECT RTRIM( 'data  12345', '123456789' );
=> 'data'

SCHEMAVERSION()

Returns the current version of database schema.

SELECT SCHEMAVERSION();
=> 1

SPACE( N )

Returns a string consisting of N space characters.

SELECT SPACE(6);
=> '      '

STRSPN( Str, CharSet )

Returns the length of the initial segment of the string Str that contains only characters from the CharSet.

SELECT strspn('aesop', 'pso')
=> 0

See also: STRCSPN(), HEAD(), TAIL()

STRCSPN( Str, CharSet )

Returns the length of the initial segment of the string Str that contains no characters from the CharSet..

SELECT STRSPN ('aesop', 'sop') 
=> 2

See also: STRSPN(), HEAD(), TAIL()

SUBSTR( str, pos [,len] )

Returns substring len characters long from string str, starting at position pos. If parameter len is not specified then returns substring from string strm starting from position pos.

SELECT SUBSTR('Valentina',6,4);
=> 'tina'
SELECT SUBSTR('Valentina',2);
=> 'alentina'
SELECT SUBSTRING('Valentina' FROM 6 FOR 4);
=> 'tina'
SELECT SUBSTRING('Valentina' FROM 2);
=> 'alentina'

TAIL( str, charSet )

TAIL( str, NOT(charSet) )

Returns the right substring of a Str that contains characters after all initial characters that are in charSet. TAIL() allows the use of operator NOT for charSet.

Note: function TAIL() can be specified by rule:

SELECT CONCAT( HEAD(s,SET), TAIL(s,SET) ) 
=> RETURNS original s

See also: STRSPN(), STRCSPN(), TAIL()

TOBIN( str )

NEW for v.5.0

Converts bit-string in the form '10101010101100101010101' into binary data that can be stored into FixedBinary or VarBinary or even BLOB field.

SELECT ToBin ( '010000010100001001000011' )
=> 'ABC'
-- insert 10 bytes value, represented as bit-string
-- Let HEX of 10 bytes is: 11 12 13 14 15 16 17 18 19 18 
INSERT INTO T1(fldFixedBinary10) VALUES( x'11 12 13 14 15 16 17 18 19 18') ) 
 
the same AS bit-string:
 
INSERT INTO T1(fldFixedBinary10) VALUES( TOBIN('00010001 00010010 00010011 00010100 00010101 00010110 00010111 00011000 00011001 00011000') ) 

See also: FROMBIN() function.

TRIM( str [, charSet] )

Returns the string str with both left and right space and, optionally charSet, characters removed.

SELECT TRIM( ' data ' );
=> 'data'
 
SELECT TRIM( '123 data 456', '123456789' );
=> 'data'

UPPER( str )

Returns the string str with all characters changed to upper case.

Note: If str is a field then its current settings affect this operation. If str is a string constant then settings are used of the database for which the query is executed.

SELECT UPPER('Hello');
=> 'HELLO'

VERSION()

Returns the version of Valentina engine (and therefore Valentina Server itself).

SELECT VERSION();
=> 2.5.8

UUID()

Returns a Universal Unique Identifier (UUID) as 36-symbols string like 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

SELECT UUID();
=> '9b4799c4-c3f1-102b-8ad5-9b951ac17a1e'