How can I rewrite my query so that functions are not being called more than once?

Question

I an working on a regular (monthly) import of a large amount of data. During the conversion, I am splitting a string into multiple columns, It is not just a simple split, though. There is a little bit of logic that decides which portion of the string goes into which field.

I have written an inline function that breaks a string into multiple parts and gives you the value at a specified index.

The parameters are:

1. string value
2. delimiter
3. index

for example:

If the string value is X4-728Z5-121-84gff and you want the function to give you the 121, then you would call the function like this:

fn_MyFunc('X4-728Z5-121-84gff', '-', 3)

My issue is this:

In my import query, the index I need for a particular field value is dependent upon the value at another index. If the value at index 1 = X4 then I want index 3, else index 4.

In the single query, I call this function 4 or 5 times, depending on the result of some case statements.

The function is basically doing the same thing over and over again... but each time, I am getting a different index. How can I reduce the efforts, such that the hard work of splitting the string is only done once, and in the same query, I can get different indexes easily?

Keep in mind that this is during an import of data from an external source, and any answer suggesting normalization or indexed views, etc will not help.

EDIT

I was asked to post my query:

SELECT
    ComplexString,
    CAST(fn_MyFunc(ComplexString, '-', 1) AS NVARCHAR(2)) AS LocationCode,
    CAST(fn_MyFunc(ComplexString, '-', 2) AS NVARCHAR(25)) AS CompanyCode,
    NULLIF(CASE
        WHEN fn_MyFunc(ComplexString, '-', 1) = 'R1' THEN NULL
        ELSE CAST(fn_MyFunc(ComplexString, '-', 3) AS INT)
    END, 0) AS ManagementType,
    CASE
        WHEN fn_MyFunc(ComplexString, '-', 1) = 'R1' THEN CAST(fn_MyFunc(ComplexString, '-', 3) AS VARCHAR(25))
        ELSE CAST(fn_MyFunc(ComplexString, '-', 4) AS NVARCHAR(25))
    END AS Network,
    .
    .
    .
FROM MyTable

Answer 1

Create a split function the splits your string to columns and use the function in a cross apply.

A function to split to 5 columns could look like this.

alter function [dbo].[SplitString]
(
    @Value nvarchar(max),
    @Delim nchar(1)
)
returns table as return
(
    select substring(T.Value, 1, T1.P - 1) as C1,
           substring(T.Value, T1.P + 1, T2.P - T1.P - 1) as C2,
           substring(T.Value, T2.P + 1, T3.P - T2.P - 1) as C3,
           substring(T.Value, T3.P + 1, T4.P - T3.P - 1) as C4,
           substring(T.Value, T4.P + 1, T5.P - T4.P - 1) as C5
    from (select @Value+replicate(@Delim, 5)) as T(Value) 
      cross apply (select charindex(@Delim, T.Value)) as T1(P)
      cross apply (select charindex(@Delim, T.Value, T1.P + 1)) as T2(P)
      cross apply (select charindex(@Delim, T.Value, T2.P + 1)) as T3(P)
      cross apply (select charindex(@Delim, T.Value, T3.P + 1)) as T4(P)
      cross apply (select charindex(@Delim, T.Value, T4.P + 1)) as T5(P)
)

And it would be used like this.

select *
from YourTable as Y
  cross apply dbo.SplitString(Y.ColumnToSplit, '-') as S

The function will be called once for each row and you can use the columns C1, C2, C3, ... in your field list or where clause without a new call the the split function.