Optimizing a Query for splitting strings with a known size

Question

I have a Column that has a . as a delimiter, it looks like so....

abc.efg.hij 

I want a query that turns this into three columns, Col1, Col2, and Col3. I am wondering what the fastest way to do this is. So far I haven't been able to do very well with my limited database experience. I've got a function:

CREATE FUNCTION [dbo].[split](
   @delimited NVARCHAR(MAX),
   @delimiter NVARCHAR(100)
 ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
 AS
 BEGIN
   DECLARE @xml XML
   SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

   INSERT INTO @t(val)
   SELECT  r.value('.','varchar(MAX)') as item
   FROM  @xml.nodes('/t') as records(r)
   RETURN
 END

This is how I'm doing it right now but I believe it could be made to go much faster, I'm also open to a significantly better function or outside the box ideas for splitting strings.I believe I'm running this dbo.split(Name, '.') three times and could only be running it once.

SELECT
      Col1 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '1'),
      Col2 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '2'),
      Col3 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '3')
FROM Mains

any help would be greatly appreciated

Answer 1

Instead of:

SELECT
  Col1 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '1'),
  Col2 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '2'),
  Col3 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '3')
FROM Mains

Use:

SELECT
  s.*
FROM Mains
CROSS APPLY (
    SELECT
        MAX(CASE WHEN Id = 1 THEN Val END) AS Col1,
        MAX(CASE WHEN Id = 2 THEN Val END) AS Col2,
        MAX(CASE WHEN Id = 3 THEN Val END) AS Col3
    FROM dbo.split(Name,'.') s
    ) s 

The idea is that you still want exactly one row per row in Mains. And using an aggregate function inside CROSS APPLY will do just that. By using CASE you only need call split() once per row.

There are plenty of questions around here about splitting strings, and SQL DB does already have a string_split() function built in.

Answer 2

As the delimiter is a dot a quite concise way of doing this would be

SELECT Col1 =  PARSENAME(name,3), 
       Col2 =  PARSENAME(name,2), 
       Col3 =  PARSENAME(name,1)
FROM Mains

The above does rely on none of the component parts being longer than 128 characters though as PARSENAME is intended for parsing dot delimited SQL Server object names and returns NULL if they won't fit into a SYSNAME

I haven't done any performance testing. I suspect the below may well perform better.

SELECT Col1 = LEFT(name, FirstDot - 1),
       Col2 = SUBSTRING(name, FirstDot + 1, SecondDot - FirstDot - 1),
       Col3 = SUBSTRING(name, 1 + SecondDot, 8000)
FROM   Mains
       CROSS APPLY (VALUES(CHARINDEX('.', name))) V1(FirstDot)
       CROSS APPLY (VALUES(CHARINDEX('.', name, 1 + FirstDot))) V2(SecondDot)