Here's a function that does exactly what you want:

CREATE FUNCTION [dbo].[itvfFindPos]

(

@strInput VARCHAR(8000)

,@delimiter 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

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b

),

E4(N) AS (SELECT 1 FROM E2 a, E2 b

),

cteTally(N) AS (SELECT 0 UNION ALL

SELECT TOP (DATALENGTH(ISNULL(@strInput,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

findchar (posnum,pos)

AS

(

SELECT

ROW_NUMBER() OVER (ORDER BY t.N) AS posnum

,CHARINDEX(@delimiter,@strInput,t.N) AS pos

FROM

cteTally t

WHERE

(SUBSTRING(@strInput,t.N,1) = @delimiter)

)

SELECT

posnum

,pos

FROM

findchar

WHERE

pos > 0

)

GO

Now create some sample data to test the function:

;WITH

SampleData(Name)

AS

(

SELECT 'sas programmer' UNION ALL

SELECT 'mms programmer' UNION ALL

SELECT 'my name is ravikumar' UNION ALL

SELECT 'i am sas programmer' UNION ALL

SELECT 'i am good in sas'

)

SELECT

s.Name

,t.posnum

,t.pos

FROM

SampleData AS s

CROSS APPLY

dbo.itvfFindPos(s.Name,'m') AS t

ORDER BY

Name

,posnum

,pos

Returns

Nameposnumpos

i am good in sas14

i am sas programmer14

i am sas programmer216

i am sas programmer317

mms programmer11

mms programmer22

mms programmer311

mms programmer412

my name is ravikumar11

my name is ravikumar26

my name is ravikumar318

sas programmer111

sas programmer212