Thursday, April 5, 2012

SQL 2000 2005 2008 2012 Find Index of Character by Occurrence in String

Description

Find Index of Character by Occurrence in String.  Sorry for not leaving an elaborate description but I feel the code is pretty straight forward.  Please feel free to ask any questions or leave any comments.

Script

-- Parameter Declaration
DECLARE @CharacterString VARCHAR(250)
    , @SearchString VARCHAR(250)
    , @Counter INT

-- Optional Number Table, See commented FROM below
DECLARE @tblNumber TABLE ( Number INT )

-- String to be searched, String to find, Set @Counter
SELECT @CharacterString = 'Life can only be understood backwards, but it ' +
    'must be lived forward.'
    , @SearchString = ' '
    , @Counter = 1

-- INSERT an Incremental Number for each character in Search String
WHILE @Counter < LEN(@CharacterString)
BEGIN
    INSERT INTO @tblNumber VALUES ( @Counter )
    SET @Counter = @Counter + 1
END

SELECT
    SearchStringOccurrence
    , CharacterIndex
    , SUBSTRING(@CharacterString,1,CharacterIndex) AS StartToFoundIndex
FROM
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY N.Number) AS SearchStringOccurrence
        , N.Number AS CharacterIndex
    FROM @tblNumber N
    --FROM (
    --    SELECT (N.number + 1) AS Number
    --    FROM [master].dbo.spt_values N
    --    WHERE N.[type] = 'P'
    --) N
    WHERE 1 = 1
        AND N.Number <= LEN(@CharacterString)
        AND SUBSTRING(@CharacterString, N.Number, DATALENGTH(@SearchString)) = @SearchString
) O
--WHERE O.SearchStringOccurrence = 4 -- "Life can only be "

Results

SearchStringOccurrence
CharacterIndex
StartToFoundIndex
1
5
Life
2
9
Life can
3
14
Life can only
4
17
Life can only be
5
28
Life can only be understood
6
39
Life can only be understood backwards,
7
43
Life can only be understood backwards, but
8
46
Life can only be understood backwards, but it
9
51
Life can only be understood backwards, but it must
10
54
Life can only be understood backwards, but it must be
11
60
Life can only be understood backwards, but it must be lived

No comments:

Post a Comment