Finding line and position of text in string

I need to get de line number and position (on that line) of a specific word in a text.

For example:

--
This is my first line.
This is my second line.
--

If I would check for ‘second’ I should get something back like: 2,12

Anyone any suggestion?

Answer

Assuming you are looking for the first occurrence per line, and assuming a LINE is delimited by char(13) and not punctuation.

Example

Declare @YourTable table (ID int,SomeText varchar(max))
Insert Into @YourTable values
(1,'This is my first line.
This is my second line.')
,(2,'This another but has a second note
Which not related to the prior "second" note')

Declare @Search varchar(100)='second'

Select A.ID
      ,Position=concat(RetSeq,',',charindex(@Search,RetVal))
 From  @YourTable A
 Cross Apply (
                Select RetSeq = row_number() over (order by 1/0)
                      ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(replace(SomeText,char(10),''),char(13),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
              ) B
 Where charindex(@Search,RetVal)>0

Returns

ID  Position
1   2,12
2   1,24
2   2,33

EDIT – Requested EDIT

Select Top 1 with Ties
       A.ID
      ,Position=concat(RetSeq,',',charindex(@Search,RetVal))
 From  @YourTable A
 Cross Apply (
                Select RetSeq = row_number() over (order by 1/0)
                      ,RetVal = B.i.value('(./text())[1]', 'varchar(max)')
                From  (Select x = Cast('<x>' + replace((Select replace(replace(SomeText,char(10),''),char(13),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
              ) B
 Where charindex(@Search,RetVal)>0
 Order by Row_Number() over (Partition By ID Order by RetSeq)

Returns

ID  Position
1   2,12
2   1,24