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