MS SQL Server split words on spaces but only when not in double-quotes

Question

I have the following function in SQL I used to take a varchar "query" string from a search page on my website. It splits the String parameters into temp table with a list of all words in that query. However I would like to incorporate the ability for users to search for phrases by enclosing words in quotes. So that a phase would basically be considered one word in my returned temp table. So basically the way it works now if you search "Gold TV" 4K it would return something like

• "Gold
• TV"
• 4K

And I would like it to return

• Gold TV
• 4K

I haven't been able to get the logic right, here is my current function. Or if there is a much better way to do this let me know.

CREATE FUNCTION [dbo].[querySplit](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
declare @idx int     
declare @slice varchar(8000)     

select @idx = 1     
    if len(@String)<1 or @String is null  return     

while @idx!= 0     
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0     
        set @slice = left(@String,@idx - 1)     
    else     
        set @slice = @String     

    if(len(@slice)>0)
        insert into @temptable(Items) values(@slice)     

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break     
end 
return     
end

Answer 1

With the use of a helper function and a CROSS APPLY

Example

Declare @S varchar(max) = 'Max "Gold TV" 4K Ultra'

Select Parsed = coalesce(B.RetVal,replace(A.RetVal,'"','')) 
 From  [dbo].[tvf-Str-Parse](replace(replace(' '+@S+' ',' "','|"'),'" ','"|'),'|') A
 Cross Apply [dbo].[tvf-Str-Parse] (case when A.RetVal like '%"%' then null else A.RetVal end,' ') B
 Where B.RetVal is not null
    or charindex('"',A.RetVal)>0
 Order By A.RetSeq,B.RetSeq

Results

Parsed
Max
Gold TV
4K
Ultra

Since you are on 2014, here is my split/parse function

CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = row_number() over (order by 1/0)
          ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);

Answer 2

Here's your UDF with a few tweaks.

CREATE FUNCTION [dbo].[querySplit] (
 @String VARCHAR(8000),
 @Delimiter CHAR(1)
)
RETURNS @Tbl TABLE (items VARCHAR(8000))
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @idx INT;
  DECLARE @slice VARCHAR(8000);
  
  SET @idx = 1;
  IF LEN(@String)<1 OR @String IS NULL RETURN;
  
  WHILE @idx != 0
  BEGIN
  
    IF LEFT(@String,1) = '"'
    BEGIN
      SET @String = STUFF(@String, 1, 1, '');
      SET @idx = CHARINDEX('"', @String, 2);
    END
    ELSE 
      SET @idx = CHARINDEX(@Delimiter, @String);
    
    IF @idx != 0
        SET @slice = LEFT(@String, @idx-1);
    ELSE
        SET @slice = @String;
    
    IF (LEN(@slice)>0)
      INSERT INTO @Tbl (Items) VALUES (@slice);
    
    SET @String = RIGHT(@String, LEN(@String) - @idx);
    IF LEN(@String) = 0 break;
  END
  RETURN;
END

select quotename(items)
from dbo.querySplit('"Diamond 3D Goggles" "5 inch×2"', ' ')

(No column name)
[Diamond 3D Goggles]
[5 inch×2]

Demo on db<>fiddle here