My input strings are like:

1. A or B or C or D or E or F
2. A OR B OR C OR D OR E OR F

Expected Output: 'A or B' OR 'C or D' OR 'E or F'

outputString = ''''   REPLACE(@vchNewValue COLLATE Latin1_General_CS_AS, ' OR ' COLLATE Latin1_General_CS_AS, ''' OR ''')   ''''

I tried using SQL Replace function and the above statement works properly for the first string and I get the desired output but for the second string since we have all the ORs in the uppercase it fails and returns 'A' OR 'B' OR 'C' OR 'D' OR 'E' OR 'F'

I'm using SSMS 15.0.

How can I solve this problem? Any help will be appreciated.

CodePudding user response：

Here's a solution that uses a UDF.

The function splits a string on a pattern as a resultset.
(similar as STRING_SPLIT, but with a pattern)

DECLARE @vchNewValue VARCHAR(100), @result VARCHAR(100);
SET @vchNewValue = 'A OR B or C OR D or E OR F';

SET @result = LTRIM(RTRIM((
       SELECT 
         CASE WHEN match = 1 
         THEN ' ' quotename(ltrim(rtrim(replace(value,' OR ',' or ') )),'''') ' ' 
         ELSE UPPER(value)
         END
       FROM dbo.fnPattern_Split(' ' @vchNewValue ' ', ' % OR % ') AS spl
       ORDER BY ordinal
       FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)')
    ));

SELECT @result AS result;

Test on db<>fiddle here

The UDF

CREATE FUNCTION dbo.fnPattern_Split
(   
  @str     VARCHAR(MAX),
  @pattern VARCHAR(100)
)
RETURNS @tbl TABLE (
 ordinal INT, 
 value VARCHAR(MAX), 
 match BIT
)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @value NVARCHAR(MAX)
        , @splitvalue NVARCHAR(MAX)
        , @startpos INT = 0
        , @endpos INT = 0
        , @ordinal INT = 0
        , @patminlen INT = LEN(REPLACE(@pattern,'%',''));
  WHILE (LEN(@str) > 0)
  BEGIN
    
    SET @startpos = ISNULL(NULLIF(PATINDEX('%' @pattern '%', @str),0), LEN(@str) 1);
    
    IF @startpos < LEN(@str)
    BEGIN
        DECLARE @foundend BIT = 0;
        SET @endpos = @startpos @patminlen;
        
        WHILE @endpos < LEN(@str) AND @foundend = 0
        BEGIN
          IF SUBSTRING(@str, @startpos, 1 @endpos-@startpos) LIKE @pattern '%'
            SET @foundend = 1;
          ELSE
            SET @endpos  = 1;
        END
        
    END
    ELSE SET @endpos = LEN(@str);
    
    IF @startpos > 1
    BEGIN
      SET @ordinal  = 1;
      SET @value = LEFT(@str, @startpos-1);
      INSERT INTO @tbl (ordinal, value, match) 
                VALUES (@ordinal, @value, 0);
    END
    
    IF @endpos > @startpos
    BEGIN
      SET @ordinal  = 1;
      SET @splitvalue = SUBSTRING(@str, @startpos, 1 @endpos-@startpos);
      INSERT INTO @tbl (ordinal, value, match) 
                VALUES (@ordinal, @splitvalue, 1);
    END
    
    SET @str = SUBSTRING(@str, @endpos 1, LEN(@str));
  END;
  RETURN;
END;

CodePudding user response：

This solution comes with a high cost on server load since while is used.

declare @input varchar(100) 
set @input = 'A or B or C or D or E or F or G or'

declare @inc int = 1, @end int = 1
,@final varchar(100) = '', @part varchar(100)
,@nextposition varchar(100), @or varchar(10)= ''
,@last varchar(10), @ifendsOR varchar(10)

select @nextposition = case when @input like '%or' then substring(@input,1,len(@input)-2) else @input end
select @ifendsOR = case when @input  like '%or' then ' or' else '' end
select @last = ltrim(rtrim(right(@nextposition,2)))

while  @end <> 0
begin 
select @part = substring(@nextposition,1,charindex('or',@nextposition)-2)
select @nextposition = replace(@nextposition,concat(@part,' or '),'')
set @end = charindex('or',@nextposition)
select @or = case when @inc%2 = 0 then ' OR ' else ' or ' end
set @inc = @inc 1
set @final = concat(@final,@part,@or)
end
select @ifendsOR = case when @inc%2 = 0 then upper(@ifendsOR) else @ifendsOR end
select concat(@final,@last,@ifendsOR)

CodePudding user response：

A recursive solution that stuffs the quotes.

declare @input varchar(100)
      , @result varchar(100);
set @input = 'A OR B or C OR D or E OR F';
set @result = @input;

with rcte as (
  select 1 as lvl
  , charindex(' or ', @input) as pos
  , len(@input) as max_pos
  union all
  select lvl 1
  , isnull(nullif(charindex(' or ', @input, pos 4), 0), max_pos) 
  , max_pos
  from rcte
  where pos < max_pos
) 
select @result = stuff(stuff(@result,pos 4,0,''''),pos,0,'''')
from rcte
where lvl%2 = 0 and pos 4 < max_pos
order by lvl desc;

SET @result = '''' @result '''';
SET @result = REPLACE(REPLACE(@result,' OR ',' or '),''' or ''',''' OR ''');

select @result as result;

Test on db<>fiddle here