Tools.Split

The Tools.Split provider enables you to write a Luminesce query that splits a series of strings into their items.

Note: The LUSID user running the query must have sufficient access control permissions to use this provider. This should automatically be the case if you are the domain owner.

You can use this provider in conjunction with other providers to generate a range of values for further querying, see example 3.

Basic usage

select * from Tools.Split where <filter-expression> in (<series_of_strings>)

Query parameters

Tools.Split has parameters that enable you to filter or refine a query.

To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Tools.Split' and FieldType = 'Parameter';

Data fields

By default, Tools.Split returns a table of data populated with particular fields (columns). You can return just a subset of these fields if you wish.

To list fields available to return, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Tools.Split' and FieldType = 'Column';

Note: Fields marked 'main' are returned by queries that start select ^ from Tools.Split...

Examples

Example 1: Splitting a basic string

select Value from Tools.Split where Original in ('a,b,c', 'c,d,e')

The table of data returned by the query looks like this:

Example 2: Splitting a series of strings and only returning unique values

select distinct value
from Tools.Split
where original in ('a,b,c', 'c,d,e')

The table of data returned by the query looks like this:

Example 3: Splitting a complex string twice with two different delimiters

In this example, we take an input of a long, complex string and use Tools.Split with the delimiters #@#@# and '~' to split the string and create table columns, rows and values.

We can then pass this table through the Tools.Pivot provider to pivot the data into a more presentable format.

@@content = select 'A''~''690235641''~''WEST AFRICAN PER''~''''~''9''~''9''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''1''~''71''~''''~''''~''#@#@#A''~''692346323''~''Ninnd''~''Jiaxing''~''11''~''9''~''''~''''~''''~''''~''Room 502''~''No. 12, Phase III, Abc''~''No. 960 Asd Road''~''Nanhu District''~''''~''''~''''~''''~''''~''1''~''43''~''140''~''''~''#@#@#A''~''694524803''~''Yaya Scandinavia''~''''~''1''~''5''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''''~''1''~''194''~''''~''''~';
@raw = select 
    sRow.[Index] as RowIndex,
    sCol.[Index] as ColumnIndex,
    sCol.Value
from
    Tools.Split sRow
    inner join Tools.Split sCol
        on sRow.[Index] = sCol.OriginalIndex
        and sCol.Original = sRow.Value
where
    sRow.DelimiterString = '#@#@#'
    and sRow.SplitThisAlone = @@content
    and sCol.DelimiterString = '''~'''
order by 1, 2
;

@pivoted =
use Tools.Pivot with @raw
--key=ColumnIndex
--aggregateColumns=Value
--columnNameFormat="f_{key}{aggregate}"
enduse;

select * from @pivoted 

Part of the table of data returned by the query looks like this:

Example 4: Splitting a string and joining with another provider

In this example, we first use Tools.Split to turn a string of space-separated currencies into a table. We then pass this table into a inner join subquery to map equity instruments to a currency from the string.  

select
    s.Value as selectedCurrency,
    equity.^
from Tools.Split s
inner join Lusid.Instrument.Equity equity
    on equity.DomCcy = s.Value
where s.Original = 'GBP USD CHF'
and s.Delimiters = ' '
LIMIT 100
;

The first few rows of the table of data returned by the query look like this:

Example 5: Splitting the output of another provider

In this example, we query Lusid.Portfolio and use Tools.Split to split any portfolios with multiple sub-holding keys into multiple rows.

select 
    p.PortfolioScope,
    p.PortfolioCode,
    s.Value as SubholdingKey
from Lusid.Portfolio p
left outer join Tools.Split s
    on p.SubHoldingKeys = s.Original
    and s.Delimiters = ','
where p.PortfolioScope = 'Finbourne-Examples'
;

The table of data returned by the query looks like this, with two rows created for the portfolio with code FundingLegWithUnderlying (which has two sub-holding keys):