Comment obtenir une chaîne qui contient une chaîne spécifique entre deux points

SELECT [DATA]
FROM(
    SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
    FROM
    (
        SELECT CAST('<X>'+REPLACE(@HotelDes, '.', '</X><X>')+'</X>' AS XML) AS String
    ) AS A
    CROSS APPLY String.nodes('/X') AS Split(a)) as q
WHERE [data] LIKE '%sauna%';

Vous devez créer une fonction de chaîne fractionnée table pour y parvenir.

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END


DECLARE @HotelDes varchar(1000) = 'Wake up to breathtaking views of the iconic Sydney Opera House, the Harbour Bridge or Darling Harbour each morning. You will be spoiled for choice at the Shangri-La Hotel, with a day spa, fitness centre and indoor pool at your disposal. The Shangri-la Sydney is located in the historic Rocks area, where Europeans first settled in Australia in 1788. This enviable location is just 5 minutes’ walk from both the Sydney Opera House and the MCA (Museum of Contemporary Art). After a day of sightseeing, you can relax in the bathtub of your lavish marble bathroom. All rooms include free Wi-Fi and an iPod dock, as well as fluffy bathrobes and slippers. CHI, The Spa offers a luxurious escape in its private spa suites, with a range of body treatments and massages on offer. The hotel also offers a hot tub, sauna and sun deck so you can make the most of your stay. The award-winning Altitude Restaurant boasts magnificent views of the harbour. Café Mix offers international all-day dining, while the New York-inspired Blu Bar on 36 is the perfect spot for a creative cocktail.';
DECLARE @HotelCat varchar(200) = 'sauna';


select splitdata+'.' from [dbo].[fnSplitString] (@HotelDes,'.')
where splitdata like '%'+@HotelCat+'%'

--Output
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 The hotel also offers a hot tub, sauna and sun deck so you can make the most of your stay.
*/

une autre option serait d'utiliser une chaîne inversée et un charindex si vous avez des caractères xml peu conviviaux pour la solution d'application croisée

with cte
  as (select 'Wake up to breathtaking views of the iconic Sydney Opera House, the Harbour Bridge or Darling Harbour each morning. You will be spoiled for choice at the Shangri-La Hotel, with a day spa, fitness centre and indoor pool at your disposal. The Shangri-la Sydney is located in the historic Rocks area, where Europeans first settled in Australia in 1788. This enviable location is just 5 minutes’ walk from both the Sydney Opera House and the MCA (Museum of Contemporary Art). After a day of sightseeing, you can relax in the bathtub of your lavish marble bathroom. All rooms include free Wi-Fi and an iPod dock, as well as fluffy bathrobes and slippers. CHI, The Spa offers a luxurious escape in its private spa suites, with a range of body treatments and massages on offer. The hotel also offers a hot tub, sauna and sun deck so you can make the most of your stay. The award-winning Altitude Restaurant boasts magnificent views of the harbour. Café Mix offers international all-day dining, while the New York-inspired Blu Bar on 36 is the perfect spot for a creative cocktail.' as sentence
              ,'sauna' as  search_str
      )
select --charindex(search_str,sentence)
       --,charindex('.',sentence,charindex(search_str,sentence)+1)
       --,substring(sentence,1,charindex('.',sentence,charindex(search_str,sentence)+1)-1)
       --reverse(substring(sentence,1,charindex('.',sentence,charindex(search_str,sentence)+1)-1))
       --,charindex('.',reverse(substring(sentence,1,charindex('.',sentence,charindex(search_str,sentence)+1)-1)))
       reverse(
               substring(
                reverse(substring(sentence,1,charindex('.',sentence,charindex(search_str,sentence)+1)-1))
                ,1
                ,charindex('.',reverse(substring(sentence,1,charindex('.',sentence,charindex(search_str,sentence)+1)-1)))-1
                )
               ) as col3
  from cte

Lien Db Fiddle

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=66d0ff1ed1

Voici une autre approche.

DECLARE @HotelDes varchar(max) = 'Wake up to breathtaking views of the iconic Sydney Opera House, the Harbour Bridge or Darling Harbour each morning. You will be spoiled for choice at the Shangri-La Hotel, with a day spa, fitness centre and indoor pool at your disposal. The Shangri-la Sydney is located in the historic Rocks area, where Europeans first settled in Australia in 1788. This enviable location is just 5 minutes’ walk from both the Sydney Opera House and the MCA (Museum of Contemporary Art). After a day of sightseeing, you can relax in the bathtub of your lavish marble bathroom. All rooms include free Wi-Fi and an iPod dock, as well as fluffy bathrobes and slippers. CHI, The Spa offers a luxurious escape in its private spa suites, with a range of body treatments and massages on offer. The hotel also offers a hot tub, sauna and sun deck so you can make the most of your stay. The award-winning Altitude Restaurant boasts magnificent views of the harbour. Café Mix offers international all-day dining, while the New York-inspired Blu Bar on 36 is the perfect spot for a creative cocktail.';
DECLARE @HotelCat varchar(200) = 'sauna';


declare @s int,@e int, @cString varchar(max), @bString varchar(max), @fString varchar(max)
set @s=1
set @e= charindex('.',@HotelDes,@s)
set  @cString=SUBSTRING(@HotelDes,@s,@e)
set @bString =SUBSTRING(@HotelDes,@e+1,1000)



while @e>1

begin

if charindex(@HotelCat,@cString,1)>0 
set @fString=@cString

set @s = 1
set @e = charindex('.',@bString,@s)
set @cString = SUBSTRING(@bString,@s,@e)
set @bString = SUBSTRING(@bString,@e+1,1000)

end

select @fString