SQL Server – Optimizing Query for Splitting Strings with Known Size

azure-sql-databasefunctionsperformancequery-performancesql serverstring

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

Best Answer

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.

Related Question