Syntax

 

<character substring function> ::=

 

 

 

SUBSTRING ( <character value expression>

FROM [ LEFT | RIGHT ] <integer expression>

[ { FOR | TO } [ LEFT | RIGHT ] <integer expression> ]

[ USING { CHARACTERS | OCTETS } ]

)

 

Alternative syntax supporting regular expression matching:

 

SUBSTRING ( <character value expression>

SIMILAR [ TO ] <character value expression> [ "IGNORE" "CASE" ]

[ "GROUP" <character value or integer expression> ]

)

 

 

 

Notes

 

The SUBSTRING function returns a subset of characters from the source string.



 

The start-position argument specifies the ordinal position within the source string where the substring starts.



 

The string-length argument specifies how many characters or octets shall be copied from the source string.



 

If no direction is specified for FROM, the LEFT is the default.

If no direction is specified for FOR | TO then the direction from FROM is the default.

 

FROM and TO are absolute positions, counting from the start (LEFT) or end (RIGHT) of the string. With 1 being the first or last character in the string. (Keeping in mind that for CHAR instead of VARCHAR the string always has the full length and is padded with spaces at the end).

 

FOR counts from the FROM position either forward (LEFT) or backward (RIGHT) to determine the absolute position. If backward, this effectively calculates the start position and the value from FROM becomes the end position.

 

If the calculated absolute start or end position is beyond the start or end of the string, it is clamped to fall within the string.

 

The string is always copied left-to-right from the absolute start to the absolute end position. (So if the end position is before the start position, an empty string is returned).



 

If CHARACTERS is specified, or the USING clause is omitted, then start-position is the ordinal character position within the source string. If OCTETS is specified, then start-position is the byte position.



 

If the declared type of the character value expression is a fixed-length or variable-length character string type, then the data type of the result is CHARACTER VARYING or NATIONAL CHARACTER VARYING with a length equal to the fixed length or maximum variable length of the character value expression, otherwise the data type of the result is CHARACTER LARGE OBJECT or NATIONAL CHARACTER LARGE OBJECT.



 

When the alternative SIMILAR [TO] regular expression syntax is used, the regular expressions are evaluated using the PCRE library.

 

NOTE! The required library to support regular expressions does not exist in Rad Studio/Delphi versions before the XE version. An exception will be raised when using the SIMILAR [TO] syntax If the SQL engine is compiled with a pre-XE version. The pre-compiled NexusDB executables fully support regular expressions.

 

A quick overview of the supported syntax can be found at

https://www.pcre.org/original/doc/html/pcresyntax.html

 

The full specification of valid patterns can be found at

https://www.pcre.org/original/doc/html/pcrepattern.html

 

The optional GROUP parameter can be either an integer or a string to

extract only the value of a numbered subpattern or named subpattern as

described in the "SUBPATTERNS" and "NAMED SUBPATTERNS" section of the full

pattern specification.

 

See below for an example of what is now possible.

 



 

If either of the arguments are null, then the result is null.



 

 

Examples

 

Expression

 

Result

 

SUBSTRING( 'Hello World' FROM 1 )

'Hello World'

SUBSTRING( 'Hello World' FROM 1 FOR 5 )

'Hello'

SUBSTRING( 'Hello World' FROM 7 )

'World'

SUBSTRING( 'Hello World' FROM 7 FOR 5 )

'World'

SUBSTRING('this is a test' FROM /*LEFT*/ 5)

is a test

SUBSTRING('this is a test' FROM /*LEFT*/ 1 FOR /*LEFT*/ 4)

this

SUBSTRING('this is a test' FROM LEFT 1 FOR /*LEFT*/ 4)

this

SUBSTRING('this is a test' FROM LEFT 4 FOR RIGHT 2)

is

SUBSTRING('this is a test' FROM RIGHT 1 FOR /*RIGHT*/ 4)

test

SUBSTRING('this is a test' FROM RIGHT 4 FOR LEFT 4)

test

SUBSTRING('this is a test' FROM RIGHT 5)

this is a

SUBSTRING('this is a test' FROM /*LEFT*/ 2 TO /*LEFT*/ 3)

hi

SUBSTRING('this is a test' FROM LEFT 2 TO LEFT 3)

hi

SUBSTRING('this is a test' FROM RIGHT 3 TO /*RIGHT*/ 2)

es

SUBSTRING('this is a test' FROM RIGHT 10 TO LEFT 10)

" is a "

 

Regular expression style:

 

 

DECLARE s NVARCHAR(53);

SET S = 'the blue king and the White king and the white queen';

 

SELECT

  SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))') AS "FULL",

  SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))' GROUP 0) AS

"GROUP_0", -- same as not specifying GROUP

  SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))' GROUP 1) AS

"GROUP_1",

  SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))' GROUP 2) AS

"GROUP_2",

  SUBSTRING(s SIMILAR TO 'the ((red|white) (king|queen))' GROUP 3) AS

"GROUP_3",

  SUBSTRING(s SIMILAR TO 'the ((?<color>red|white) (?<gender>king|queen))'

GROUP 'color') AS "GROUP_COLOR",

  SUBSTRING(s SIMILAR TO 'the ((?<color>red|white) (?<gender>king|queen))'

GROUP 'gender') AS "GROUP_GENDER",

  SUBSTRING(s SIMILAR TO 'the (red|white) (?<gender>king|queen)' IGNORE

CASE GROUP 'gender') AS "GROUP_GENDER_IGNORECASE"

FROM

  #dummy;

 

which will return the following values:

 

FULL: the white queen

GROUP_0: the white queen

GROUP_1: white queen

GROUP_2: white

GROUP_3: queen

GROUP_COLOR: white

GROUP_GENDER: queen

GROUP_GENDER_IGNORECASE: king 

 

 

 

 

 


 

Conformance

 

SQL:2003 standard

-

Core SQL