Mixing
How to get a string which contains a specific string between two full stop
2
In case it is too confusing, let me give you an example for better understanding.
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';
so what I want is string which contains word "sauna" and that string will be between two full stop.
I want the text to show "The hotel also offers a hot tub, sauna and sun deck so you can make the most of your stay" between two full stops.
I have tried:
SELECT SUBSTRING(@HotelDes,CHARINDEX('.',@HotelDes)+1, CHARINDEX('.',@HotelDes,CHARINDEX('.',@HotelDes)+1) -CHARINDEX('.',@HotelDes)-1)
This returns a string of " You will be spoiled for choice at the Shangri-La Hotel, with a day spa, fitness centre and indoor pool at your disposal" from first full stop.
Does anyone have an idea of how to get the string with "sauna" text?
sql
sql-server sql-server-2012 edited Jan 2 at 8:57
Julia
519
asked Jan 2 at 7:54
Saurabh SolankiSaurabh Solanki
1,450825
add a comment |
2
In case it is too confusing, let me give you an example for better understanding.
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';
so what I want is string which contains word "sauna" and that string will be between two full stop.
I want the text to show "The hotel also offers a hot tub, sauna and sun deck so you can make the most of your stay" between two full stops.
I have tried:
SELECT SUBSTRING(@HotelDes,CHARINDEX('.',@HotelDes)+1, CHARINDEX('.',@HotelDes,CHARINDEX('.',@HotelDes)+1) -CHARINDEX('.',@HotelDes)-1)
This returns a string of " You will be spoiled for choice at the Shangri-La Hotel, with a day spa, fitness centre and indoor pool at your disposal" from first full stop.
Does anyone have an idea of how to get the string with "sauna" text?
sql
sql-server sql-server-2012 edited Jan 2 at 8:57
Julia
519
asked Jan 2 at 7:54
Saurabh SolankiSaurabh Solanki
1,450825
add a comment |
2
2
2
1
In case it is too confusing, let me give you an example for better understanding.
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';
so what I want is string which contains word "sauna" and that string will be between two full stop.
I want the text to show "The hotel also offers a hot tub, sauna and sun deck so you can make the most of your stay" between two full stops.
I have tried:
SELECT SUBSTRING(@HotelDes,CHARINDEX('.',@HotelDes)+1, CHARINDEX('.',@HotelDes,CHARINDEX('.',@HotelDes)+1) -CHARINDEX('.',@HotelDes)-1)
This returns a string of " You will be spoiled for choice at the Shangri-La Hotel, with a day spa, fitness centre and indoor pool at your disposal" from first full stop.
Does anyone have an idea of how to get the string with "sauna" text?
sql
sql-server sql-server-2012 edited Jan 2 at 8:57
Julia
519
asked Jan 2 at 7:54
Saurabh SolankiSaurabh Solanki
1,450825
In case it is too confusing, let me give you an example for better understanding.
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';
so what I want is string which contains word "sauna" and that string will be between two full stop.
I want the text to show "The hotel also offers a hot tub, sauna and sun deck so you can make the most of your stay" between two full stops.
I have tried:
SELECT SUBSTRING(@HotelDes,CHARINDEX('.',@HotelDes)+1, CHARINDEX('.',@HotelDes,CHARINDEX('.',@HotelDes)+1) -CHARINDEX('.',@HotelDes)-1)
This returns a string of " You will be spoiled for choice at the Shangri-La Hotel, with a day spa, fitness centre and indoor pool at your disposal" from first full stop.
Does anyone have an idea of how to get the string with "sauna" text?
sql
sql-server sql-server-2012 sql
sql-server sql-server-2012 edited Jan 2 at 8:57
Julia
519
asked Jan 2 at 7:54
Saurabh SolankiSaurabh Solanki
1,450825
edited Jan 2 at 8:57
Julia
519
asked Jan 2 at 7:54
Saurabh SolankiSaurabh Solanki
1,450825
edited Jan 2 at 8:57
Julia
519
edited Jan 2 at 8:57
Julia
519
edited Jan 2 at 8:57
Julia
519
519
asked Jan 2 at 7:54
Saurabh SolankiSaurabh Solanki
1,450825
asked Jan 2 at 7:54
Saurabh SolankiSaurabh Solanki
1,450825
asked Jan 2 at 7:54
Saurabh SolankiSaurabh Solanki
1,450825
1,450825
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
1
Here is another approach.
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
answered Jan 2 at 9:38
MD AZAD HUSSAINMD AZAD HUSSAIN
1726
add a comment |
3
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%';
answered Jan 2 at 8:10
CodeManCodeMan
557311
add a comment |
1
another option would be to use reverse string and charindex if you have xml unfriendly characters for cross apply solution
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
Db Fiddle link
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=66d0a45fe0dac0c6ddacff421d341ed1
answered Jan 2 at 8:38
George JosephGeorge Joseph
1,590510
add a comment |
1
You need to create table-valued split string function to achieve that.
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.
*/
edited Jan 2 at 9:00
Dale Burrell
3,39052655
answered Jan 2 at 8:12
Will KongWill Kong
713
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002969%2fhow-to-get-a-string-which-contains-a-specific-string-between-two-full-stop%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Here is another approach.
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
answered Jan 2 at 9:38
MD AZAD HUSSAINMD AZAD HUSSAIN
1726
add a comment |
1
Here is another approach.
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
answered Jan 2 at 9:38
MD AZAD HUSSAINMD AZAD HUSSAIN
1726
add a comment |
1
1
1
Here is another approach.
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
answered Jan 2 at 9:38
MD AZAD HUSSAINMD AZAD HUSSAIN
1726
Here is another approach.
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
answered Jan 2 at 9:38
MD AZAD HUSSAINMD AZAD HUSSAIN
1726
answered Jan 2 at 9:38
MD AZAD HUSSAINMD AZAD HUSSAIN
1726
answered Jan 2 at 9:38
MD AZAD HUSSAINMD AZAD HUSSAIN
1726
answered Jan 2 at 9:38
MD AZAD HUSSAINMD AZAD HUSSAIN
1726
1726
add a comment |
add a comment |
3
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%';
answered Jan 2 at 8:10
CodeManCodeMan
557311
add a comment |
3
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%';
answered Jan 2 at 8:10
CodeManCodeMan
557311
add a comment |
3
3
3
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%';
answered Jan 2 at 8:10
CodeManCodeMan
557311
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%';
answered Jan 2 at 8:10
CodeManCodeMan
557311
answered Jan 2 at 8:10
CodeManCodeMan
557311
answered Jan 2 at 8:10
CodeManCodeMan
557311
answered Jan 2 at 8:10
CodeManCodeMan
557311
557311
add a comment |
add a comment |
1
another option would be to use reverse string and charindex if you have xml unfriendly characters for cross apply solution
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
Db Fiddle link
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=66d0a45fe0dac0c6ddacff421d341ed1
answered Jan 2 at 8:38
George JosephGeorge Joseph
1,590510
add a comment |
1
another option would be to use reverse string and charindex if you have xml unfriendly characters for cross apply solution
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
Db Fiddle link
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=66d0a45fe0dac0c6ddacff421d341ed1
answered Jan 2 at 8:38
George JosephGeorge Joseph
1,590510
add a comment |
1
1
1
another option would be to use reverse string and charindex if you have xml unfriendly characters for cross apply solution
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
Db Fiddle link
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=66d0a45fe0dac0c6ddacff421d341ed1
answered Jan 2 at 8:38
George JosephGeorge Joseph
1,590510
another option would be to use reverse string and charindex if you have xml unfriendly characters for cross apply solution
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
Db Fiddle link
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=66d0a45fe0dac0c6ddacff421d341ed1
answered Jan 2 at 8:38
George JosephGeorge Joseph
1,590510
answered Jan 2 at 8:38
George JosephGeorge Joseph
1,590510
answered Jan 2 at 8:38
George JosephGeorge Joseph
1,590510
answered Jan 2 at 8:38
George JosephGeorge Joseph
1,590510
1,590510
add a comment |
add a comment |
1
You need to create table-valued split string function to achieve that.
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.
*/
edited Jan 2 at 9:00
Dale Burrell
3,39052655
answered Jan 2 at 8:12
Will KongWill Kong
713
add a comment |
1
You need to create table-valued split string function to achieve that.
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.
*/
edited Jan 2 at 9:00
Dale Burrell
3,39052655
answered Jan 2 at 8:12
Will KongWill Kong
713
add a comment |
1
1
1
You need to create table-valued split string function to achieve that.
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.
*/
edited Jan 2 at 9:00
Dale Burrell
3,39052655
answered Jan 2 at 8:12
Will KongWill Kong
713
You need to create table-valued split string function to achieve that.
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.
*/
edited Jan 2 at 9:00
Dale Burrell
3,39052655
answered Jan 2 at 8:12
Will KongWill Kong
713
edited Jan 2 at 9:00
Dale Burrell
3,39052655
edited Jan 2 at 9:00
Dale Burrell
3,39052655
edited Jan 2 at 9:00
Dale Burrell
3,39052655
3,39052655
answered Jan 2 at 8:12
Will KongWill Kong
713
answered Jan 2 at 8:12
Will KongWill Kong
713
answered Jan 2 at 8:12
Will KongWill Kong
713
713
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002969%2fhow-to-get-a-string-which-contains-a-specific-string-between-two-full-stop%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
