---
title: "Tools.Split"
slug: "toolssplit"
updated: 2024-08-21T14:09:38Z
published: 2024-08-21T14:09:38Z
canonical: "support.lusid.com/toolssplit"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Tools.Split

| **Type** | **Read/write** | **Author** | **Availability** |
| --- | --- | --- | --- |
| [Data provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider) | Read | Finbourne | Provided with LUSID |

The `Tools.Split` provider enables you to write a [Luminesce query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that splits a series of strings into their items.

> **Note:** The LUSID user running the query must have sufficient [access control permissions](/v1/docs/creating-policies-to-control-access-to-providers-for-different-luminesce-end-users) 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](/v1/docs/toolssplit#example-3-splitting-a-complex-string-twice-with-two-different-delimiters).

## Basic usage

```sql
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](/v1/docs/what-tools-are-available-to-write-luminesce-queries):

```sql
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](/v1/docs/what-tools-are-available-to-write-luminesce-queries):

```sql
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

```sql
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: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/273ed46c-af9d-4e78-90f1-4a767ff238a5.png)

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

```sql
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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/bc02da37-9967-4ec1-9f07-00c5a539e29d.png)

### 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.

```sql
@@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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/2ab55fdf-8d5c-4afc-93ee-48ee0ad62280.png)

### 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.

```sql
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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/57b0e36e-29e8-4de3-9162-4c93a92cd6c2.png)

### 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.

```sql
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):

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/22246a00-1603-4bea-8044-833ba616bfb9.png)