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

Question

I am working on regular (monthly) import of a large amount of data. During the conversion, I split the row into several columns, but this is not just a simple split. There is some logic that decides which part of the line goes into which field.

I wrote a built-in function that breaks a string into several parts and gives you the value at the specified index.

Parameters:

• string value
• Delimiter
• Index

eg:

If the string value is X4-728Z5-121-84gff and you want the function to give you 121, you call the function as follows:

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

My problem is this:

In my imported query, the index that I need for a specific field value depends on the value in another index. If the value in index 1 = X4 , then I want index 3, otherwise index 4.

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

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

Keep in mind that this happens when importing data from an external source, and any answer suggesting normalization or indexed views etc. will not help.

EDIT

I was asked to send my request:

 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 by dividing your row into columns and using the function in cross apply .

The 5-column split function might 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 will be used like that.

 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 columns C1, C2, C3, ... in your list of fields or where a sentence without a new call is a split function.

Answer 2

If the maximum of all lines is 4 parts, instead of your function, you can simply do this inline:

 SELECT PARSENAME(REPLACE(column, '-', '.'), CASE WHEN (condition for 4th element) THEN 1 WHEN (condition for 3rd element) THEN 2 WHEN (condition for 2nd element) THEN 3 WHEN (condition for 1st element) THEN 4 END FROM ... 

You may also consider:

(a) storing each part of the row in a separate computed column. You can even save / index the calculated column.

(b) storing individual parts of a string separately in the first place - concatenation is always easier than splitting.

EDIT based on updated request

 ;WITH x AS ( SELECT ComplexString, p1 = LEFT(ComplexString, 2), p2 = dbo.fn_MyFunc(ComplexString, '-', 2), p3 = dbo.fn_MyFunc(ComplexString, '-', 3), p4 = dbo.fn_MyFunc(ComplexString, '-', 4) -- , other columns FROM dbo.MyTable ) SELECT  ComplexString, p1 AS LocationCode,  LEFT(p2, 25) AS CompanyCode,  CASE WHEN p1 <> 'RI' THEN CONVERT(INT, LEFT(p3, 3)) ELSE 0 END AS ManagementType,  LEFT(CASE WHEN p1 = 'RI' THEN p3 ELSE p4 END, 25) AS Network FROM x; 

Answer 3

If the individual parts of the row are central to the operations that you want to perform on your data, you could continue to normalize by storing the details both in separate columns and in a related table.

As an alternative, a table function that returns individual parts will allow you to improve performance if you need to access parts repeatedly.

Answer 4

If your line has a fixed (or a certain maximum) number of parts, you can create a set of variables such as @ part1, @ part2, etc. (using the best names if each part has a specific meaning). Use your function to populate variables once and use variables instead of parsing again.

- OR -

Convert a row to a narrow temporary table or a table variable with one varchar column and one tinyint identifier column using the parsing method (there are a number of posts in SO related topics - and here is a good csv-oriented article, but easily adaptable to your situation) . Then you can use the sequence column in select to find the row you need.