March 2, 2017 at 3:39 pm
Please
DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)
SET @String ='The SQL SERVER is one of the best applications of the Microsoft Worrld'
SET @Search_String='the'
SELECT CHARINDEX(@Search_String,@String) As [First occurrence]--first occurence
--Find Last occurrence of any character/word in the string
SELECT DATALENGTH(@String)-CHARINDEX(REVERSE(@Search_String),REVERSE(@String))-1 As [Last occurrence]
March 2, 2017 at 6:47 pm
mw112009 - Thursday, March 2, 2017 3:39 PMPlease
DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)SET @String ='The SQL SERVER is one of the best applications of the Microsoft Worrld'
SET @Search_String='the'SELECT CHARINDEX(@Search_String,@String) As [First occurrence]--first occurence
--Find Last occurrence of any character/word in the string
SELECT DATALENGTH(@String)-CHARINDEX(REVERSE(@Search_String),REVERSE(@String))-1 As [Last occurrence]
Do you actually need to know the character position of each occurrence in the string or are you just going for a count of the number of occurances?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2017 at 6:49 am
Would like the position of each occurrence in a comma separated string.
March 3, 2017 at 11:02 am
If you're going to use this on a regular basis I would probably turn it into a Scalar-valued Function so you can just pass in the string and search string and get back the comma separated results.
DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)
SET @String ='The SQL SERVER is one of the best applications of the Microsoft Worrld'
SET @Search_String='the'
declare @i int = 1
declare @stringLen int = LEN(@Search_String)
declare @Result varchar(100) = ''
WHILE @i < LEN(@String)
BEGIN
if SUBSTRING(@String,@i,@stringLen) = @Search_String
begin
set @Result = @Result + CAST(@i as varchar(100)) + ','
end
SET @i = @i + 1
END
SET @Result = SUBSTRING(@Result,1,LEN(@Result) -1)
SELECT @Result
March 3, 2017 at 11:37 am
Thanks, out of curiosity is there a way to get the start position of the nth occurrence ( if any ) without going into a WHILE LOOP.
Never mind if it is not there.
March 3, 2017 at 11:42 am
Jeff Atherton - Friday, March 3, 2017 11:02 AMIf you're going to use this on a regular basis I would probably turn it into a Scalar-valued Function so you can just pass in the string and search string and get back the comma separated results.
DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)SET @String ='The SQL SERVER is one of the best applications of the Microsoft Worrld'
SET @Search_String='the'declare @i int = 1
declare @stringLen int = LEN(@Search_String)
declare @Result varchar(100) = ''WHILE @i < LEN(@String)
BEGINif SUBSTRING(@String,@i,@stringLen) = @Search_String
begin
set @Result = @Result + CAST(@i as varchar(100)) + ','
end
SET @i = @i + 1
ENDSET @Result = SUBSTRING(@Result,1,LEN(@Result) -1)
SELECT @Result
not sure about this result......?? EDIT >>> what I was attempting to say was is the OP looking for the string "the" or the word "the" ...code given is returning the string
DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)
SET @String ='The SQL SERVER is one of the best applications of the Microsoft World and therefore is any further proof required?'
SET @Search_String='the'
declare @i int = 1
declare @stringLen int = LEN(@Search_String)
declare @Result varchar(100) = ''
WHILE @i < LEN(@String)
BEGIN
if SUBSTRING(@String,@i,@stringLen) = @Search_String
begin
set @Result = @Result + CAST(@i as varchar(100)) + ','
end
SET @i = @i + 1
END
SET @Result = SUBSTRING(@Result,1,LEN(@Result) -1)
SELECT @Result
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 3, 2017 at 11:47 am
Why do you want to avoid the WHILE loop? I can't see this being a performance issue and if so what size string are you searching? And if you just don't want to deal with the WHILE loop then put it in a function and just call the function when you need it. Then you or your users never have to even know that a WHILE loop is being used.
March 3, 2017 at 11:50 am
Here's an option that can be easily converted into an Inline Table-Valued function.
DECLARE @String AS VARCHAR(100),
@Search_String AS VARCHAR(100),
@Ocurrence int;
SELECT @String ='The SQL SERVER is one of the best applications of the Microsoft Worrld',
@Search_String='the',
@Ocurrence = 3;
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(LEN(@String) - LEN(@Search_String) + 1) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
),
cteOcurrences AS(
SELECT n Position, ROW_NUMBER() OVER(ORDER BY n) Ocurrence
FROM cteTally
WHERE SUBSTRING(@String, n, LEN(@Search_String)) = @Search_String
)
SELECT *
FROM cteOcurrences
WHERE Ocurrence = @Ocurrence;
March 3, 2017 at 11:52 am
Jeff Atherton - Friday, March 3, 2017 11:47 AMWhy do you want to avoid the WHILE loop? I can't see this being a performance issue and if so what size string are you searching? And if you just don't want to deal with the WHILE loop then put it in a function and just call the function when you need it. Then you or your users never have to even know that a WHILE loop is being used.
Why do you want to leave a potential performance problem available for anyone? What will happen when they start running this against millions of rows?
Putting the while loop into a function will only mask the problem.
March 3, 2017 at 12:00 pm
J Livingston SQL - Friday, March 3, 2017 11:42 AMnot sure about this result......??
DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)SET @String ='The SQL SERVER is one of the best applications of the Microsoft World and therefore is any further proof required?'
SET @Search_String='the'declare @i int = 1
declare @stringLen int = LEN(@Search_String)
declare @Result varchar(100) = ''WHILE @i < LEN(@String)
BEGINif SUBSTRING(@String,@i,@stringLen) = @Search_String
begin
set @Result = @Result + CAST(@i as varchar(100)) + ','
end
SET @i = @i + 1
ENDSET @Result = SUBSTRING(@Result,1,LEN(@Result) -1)
SELECT @Result
Good catch. Thanks for the QA Testing. Here's an Update:
DECLARE @String AS VARCHAR(100)
DECLARE @Search_String AS VARCHAR(100)
SET @String ='The SQL SERVER is one of the best applications of the Microsoft World and therefore is any further proof required?'
SET @Search_String='the '
declare @i int = 1
declare @stringLen int = LEN(@Search_String)
declare @Result varchar(100) = ''
WHILE @i < LEN(@String)
BEGIN
if (SUBSTRING(@String,@i,@stringLen) = @Search_String) AND (SUBSTRING(@String,@i + @stringLen,1) = CHAR(32))
begin
set @Result = @Result + CAST(@i as varchar(100)) + ','
end
SET @i = @i + 1
END
SET @Result = SUBSTRING(@Result,1,LEN(@Result) -1)
SELECT @Result
Although this would probably still miss one if the search string was the last word in the string.
March 3, 2017 at 12:05 pm
Luis Cazares - Friday, March 3, 2017 11:52 AMWhy do you want to leave a potential performance problem available for anyone? What will happen when they start running this against millions of rows?
Putting the while loop into a function will only mask the problem.
Is it a problem though? That was my question. What's the data size? How do you they plan on using it? Only need it for one record every now and then? Or do they really need to run this against a set of millions of records in one pass? :unsure:
March 3, 2017 at 12:17 pm
Jeff Atherton - Friday, March 3, 2017 12:05 PMLuis Cazares - Friday, March 3, 2017 11:52 AMWhy do you want to leave a potential performance problem available for anyone? What will happen when they start running this against millions of rows?
Putting the while loop into a function will only mask the problem.Is it a problem though? That was my question. What's the data size? How do you they plan on using it? Only need it for one record every now and then? Or do they really need to run this against a set of millions of records in one pass? :unsure:
Remember Murphy's Law: Anything that can go wrong, will go wrong.
At the moment it might not be a problem, but why would you want to wait until it is urgent to code it the best possible way? I've had to fix a lot of legacy code because people thought that it wasn't a problem at the moment.
March 3, 2017 at 12:26 pm
Luis Cazares - Friday, March 3, 2017 12:17 PMRemember Murphy's Law: Anything that can go wrong, will go wrong.At the moment it might not be a problem, but why would you want to wait until it is urgent to code it the best possible way? I've had to fix a lot of legacy code because people thought that it wasn't a problem at the moment.
The way the question was stated the request was to pass a string and a search string and then get back a comma separated list of the starting positions for each occurrence of that search string. That sounds a lot like this is going to be one at a time. Now if they need to do a SELECT and have this result as a column for millions of records then that's another story. But that's not what was stated.
March 3, 2017 at 2:03 pm
And then it actually turns out the string to be searched is actually a column in a table with a million rows of data.
March 3, 2017 at 6:39 pm
mw112009 - Friday, March 3, 2017 6:49 AMWould like the position of each occurrence in a comma separated string.
Apologies for the questions but I want to make sure that we get it right for you. When you say "occurrence", do you mean only whole words separated by spaces or should the instance of "the" in the word (for example) "brother" count as an instance of what you're looking for, as well?
Also, what is this for? I mean what are you using this search for? If we knew the end goal, we might be able to find a better way.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply