Charindex values of a substring inside a long sentence

  • 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.

  • 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

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • 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
  • This was removed by the editor as SPAM

  • 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 weeks, 6 days ago by  Matthew145.
  • This was removed by the editor as SPAM

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

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