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

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

CodePudding user response：

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 A.RetVal is not null
 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)
);

CodePudding user response：

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]

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

Demo on db<>fiddle here