Charindex values of a substring inside a long sentence

Question

Post reply

Charindex values of a substring inside a long sentence

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
May 26, 2022 at 11:39 pm

#4039931

I need to gather the starting position of the keyword "sql" from a sql statement from a table so that I can run through all the occurrences to print it. This is just an example.
declare @expression varchar(30) = 'Practice sql server daily. SQL Server is Important, SQL Server is a great db platform';
select charindex ('sql', @expression, 10, 3)

Can you help me with how to write the query to put into a table of the stating index values of the occurrences?
Thanks.
May 27, 2022 at 7:48 am

This was removed by the editor as SPAM
May 27, 2022 at 7:49 am

This was removed by the editor as SPAM

June 1, 2022 at 7:10 am

This was removed by the editor as SPAM
June 28, 2022 at 9:46 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

a simple google search finds a few hits.

one of them is https://dba.stackexchange.com/questions/41961/how-to-find-all-positions-of-a-string-within-another-string

DesNorton SSC-Insane Points: 24381 More actions · Answer 2

I would create an iTVF to search a string for the starting positions of a keyword

CREATE FUNCTION dbo.GetAllStartingPositions (
    @String  varchar(8000)
  , @KeyWord varchar(5)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max -- enough to cover varchar(8000)
 cteTally(N) AS (--==== Limit the number of rows up front for a performance gain and also prevention of accidental "overruns"
                 SELECT TOP (LEN(ISNULL(@String,''))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                )
  --==== Return the starting positions
  SELECT DelimStartPos = t.N
  FROM  cteTally t
  WHERE SUBSTRING(@String, t.N, LEN(@KeyWord)) = @KeyWord;

Then you can call the function as a stand-alone

DECLARE @expression varchar(100) = 'Practice sql server daily. SQL Server is Important, SQL Server is a great db platform';

SELECT p.DelimStartPos
FROM dbo.GetAllStartingPositions(@expression, 'sql') AS p;

or you can use it with a table of data

DECLARE @YourTable table (ID int IDENTITY(1,1), expression varchar(100));

INSERT INTO @YourTable (expression)
VALUES ('Practice sql server daily')
     , ('SQL Server is Important, SQL Server is a great db platform')
     , ('The element is not found in here')

SELECT t.ID, p.DelimStartPos
FROM @YourTable AS t
/********************************************************************************
***  You need to decide which of the following APPLY operators to use
***  CROSS APPLY will not return anything for ID=3 because it does not contain "sql"
***  OUTER APPLY will return ID=3, but DelimStartPos will be NULL
********************************************************************************/CROSS APPLY dbo.GetAllStartingPositions(t.expression, 'sql') AS p
--OUTER APPLY dbo.GetAllStartingPositions(t.expression, 'sql') AS p

How to post data/code on a forum to get the best help.

Matthew145 Grasshopper Points: 17 More actions · Answer 3

I'm especially stayed aware of the article and I will get many benefits from it. Subsequently, thank you for sharing it.

www.epayitonline.com

This reply was modified 2 years, 5 months ago by Matthew145.