convert to output table using table A and Table B

Question

Please see the picture

Answer 1

Check it out.
It will work starting from SQL Server 2017 onwards.
If needed, it is very easy to make it work on SQL Server 2012 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl_a TABLE (ID INT IDENTITY PRIMARY KEY, out_days VARCHAR(100));
INSERT INTO @tbl_a (out_days) VALUES
('MondayTuesdayWednesdayThursdayFriday'),
('MondayTuesdayWednesday');

DECLARE @tbl_b TABLE (ID INT IDENTITY PRIMARY KEY, WeekDayName VARCHAR(20), abbr CHAR(3));
INSERT INTO @tbl_b (WeekDayName, abbr) VALUES
('Sunday', 'Sun'),
('Monday', 'Mon'),
('Tuesday', 'Tue'),
('Wednesday', 'Wed'),
('Thursday', 'Thu'),
('Friday', 'Fri'),
('Saturday', 'Sat')
-- DDL and sample data population, end

-- just to see
SELECT * FROM @tbl_a;
SELECT * FROM @tbl_b;

DECLARE @WeekDayName VARCHAR(20), @ReplaceWith CHAR(3)
 , @openBracket CHAR(1) = '[', @closeBracket CHAR(1) = ']'
 , @RowCount INT = (SELECT COUNT(*) FROM @tbl_b);

DECLARE @separator CHAR(2) = @closeBracket + @openBracket;

WHILE @RowCount > 0 BEGIN
   SELECT @WeekDayName=WeekDayName, @ReplaceWith=abbr
   FROM @tbl_b 
   ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;

   -- do whatever needed, apply any logic, call stored procedures, etc.
   UPDATE @tbl_a
   SET out_days = REPLACE(out_days, @WeekDayName, CONCAT(@openBracket, @ReplaceWith, @closeBracket))

   SET @RowCount -= 1;
END

;WITH rs AS
(
 SELECT * 
 , TRY_CAST('<root><r>' + 
    REPLACE(TRIM('[]' FROM out_days), @separator, '</r><r>') + 
    '</r></root>' AS XML) AS xmldata
 FROM @tbl_a
)
SELECT ID, out_days as [before]
 , rs.xmldata
 , xmldata.query('concat((/root/r[1]/text())[1], "-", (/root/r[last()]/text())[1])') 
 .value('.', 'CHAR(7)') AS out_days
FROM rs;

Output

+----+---------------------------+----------+
| ID |          before           | out_days |
+----+---------------------------+----------+
|  1 | [Mon][Tue][Wed][Thu][Fri] | Mon-Fri  |
|  2 | [Mon][Tue][Wed]           | Mon-Wed  |
+----+---------------------------+----------+

Answer 2

Hi @madddddd ,

Welcome to Microsoft Q&A!

Please also refer below method:

First step : create one function which could split one string based on upper case.

create Function dbo.Split_On_Upper_Case(@Temp VarChar(1000))  
Returns VarChar(1000)  
AS  
Begin  
  
    Declare @KeepValues as varchar(50)  
    Set @KeepValues = '%[^ ][A-Z]%'  
    While PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) > 0  
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) + 1, 0, ' ')  
  
    Return @Temp  
End  

Second step:

CREATE TABLE TABLEA(ID INT IDENTITY PRIMARY KEY, out_days VARCHAR(100));  
 INSERT INTO TABLEA (out_days) VALUES  
 ('MondayTuesdayWednesdayThursdayFriday'),  
 ('MondayTuesdayWednesday');  
      
CREATE TABLE TABLEB (ID INT IDENTITY PRIMARY KEY, WeekDayName VARCHAR(20), abbr CHAR(3));  
 INSERT INTO TABLEB (WeekDayName, abbr) VALUES  
 ('Sunday', 'Sun'),  
 ('Monday', 'Mon'),  
 ('Tuesday', 'Tue'),  
 ('Wednesday', 'Wed'),  
 ('Thursday', 'Thu'),  
 ('Friday', 'Fri'),  
 ('Saturday', 'Sat')  
  
;with cte as (  
SELECT id, CASE WHEN out_days LIKE '% %' THEN LEFT(out_days, Charindex(' ', out_days) - 1) ELSE out_days END [start],  
       CASE WHEN out_days LIKE '% %' THEN RIGHT(out_days, Charindex(' ', Reverse(out_days)) - 1) END [end]  
FROM   ( Select id,dbo.Split_On_Upper_Case(out_days) out_days from TABLEA)  a)  

 select a.ID,upper(b.abbr)+'-'+upper(c.abbr) out_days  
 from cte a  
 inner join TABLEB b on a.[start]=b.WeekDayName   
 inner join TABLEB c on a.[end]=c.WeekDayName   
 order by a.ID  

Output:

ID out_days  
1 MON-FRI  
2 MON-WED  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Check this query too:

select a.ID, b1.Abbr + '-' + b2.Abbr as out_days
from TableA a
inner join TableB b1 on a.out_days like b1.LongName + '%'
inner join TableB b2 on a.out_days like '%' + b2.LongName