| Type | Read/write | Author | Availability |
| Data provider | Read | Finbourne | Provided with LUSID |
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):