Sub string projection
Projection – Abstract
Purpose: Returns a section from a string determined by a projection.
The Sub string projection returns a section from a string determined by the Projection.
The optional Begin index and Max. length parameters control which section of the return value from the projection is returned as a 'substring'.
Configuration
CAUTION
Depending on the database system used, the results of a Sub string projection can vary significantly. The user interface presented here technically allows parameterizations that only work on certain databases.
The documentation in the following section is limited to parameter combinations that provide the described results regardless of the database system used.
Different parameter combinations are technically feasible in practice, provided that the desired results are achieved in tests with the database system used.
However, it should be remembered that if the database system for an existing implementation is changed at a later date, or if solution components are exchanged between Lobster Data Platform / Orchestration systems that use different database systems, serious malfunctions and significant deviations from targeted processing logics may then occur, the cause of which may only be localizable with considerable effort and may not be completely remedied in the context of the 'alternative' database system.
In the following table, the green shaded cells refer to the use of the parameters, which should be guaranteed regardless of the database system.
The yellow shading, on the other hand, indicates references to the use of the parameters outside the 'strict' default.
|
Parameter |
Type |
Description |
|
Name |
String |
The Name parameter optionally defines an alias name for the projection. If no Name is specified, the name of the Projection with the static prefix 'substring of ' will be used as the name. |
|
Projection |
String |
A Projection (see Projections) of any type (e.g. also a Literal projection) can be used as a projection as long as it returns a String value.
|
|
If the projection returns a data type other than a String, a type conversion to a String takes place or an error occurs, depending on the data type and the database system. |
||
|
Begin index
|
Integer |
The Begin index defines the position within the sequence obtained from the Projection via an Integer value, from which a substring is taken over for the return value.
A positive Integer value must be available as the Begin index at runtime so that the result of the Sub string projection does not depend on database-specific peculiarities.
|
|
If there is no value ($null), the value 0, a negative value or a type other than Integer for the Begin index, it is not possible to assume a uniform result for different database systems. |
||
|
Max. length |
Integer |
The Max. length parameter defines an upper limit for the length of the string in the return value via an Integer value. However, the returned 'substring' ends with the last character from the Projection at the latest.
The same options and restrictions apply for the parameterization as for the Begin index. |
|
If there is no value ($null), a negative value or any type other than Integer for the Begin index, it is not possible to assume a uniform result for different database systems. |
Examples
Simple example: Concatenate country code and postal code area
|
Based on the 'country' (countryCode) and 'ZIP code' (zipcode) properties, a composite code for the REGION is formed in the context of a tuple search, by which the search result can be sorted or filtered, for example. Specifically, the first two digits (or characters) of the 'ZIP code' are concatenated with the internal abbreviation for Country to obtain a four-digit code when both fields are properly populated. The table on the right shows sample data for a selection of cities in Germany and Switzerland in the first column (REGION). |
REGION,AREA,CITY |
Configuration:
|
The screenshot on the right shows the configuration for the projection of the first column (REGION):
|
|
More complex example: A variable number of digits depending on the length of the ZIP code
Based on the previous use case, a variable number of leading digits from the ZIP code (zipcode) is now transferred to the identifier for the REGION, depending on the 'country' (countryCode),
Specifically, only the first digit of four-digit ZIP codes is transferred, and the first two characters of all other ZIP codes.
Configuration:
|
The screenshot shows the customized configuration for the ZIP code component within the Concatenated projection for the REGION column:
|
|
Runtime example:
REGION,AREA,CITY |