This will work for SQl 2000 or later only. It uses fn_Split function from http://www.sqlmag.com/Articles/Index.cfm?ArticleID=21071.

-- Create function ufn_GetVal01
CREATE FUNCTION ufn_GetVal01(@SearchFor varchar(80), @Notes varchar(1024))
RETURNS varchar(80)
AS
BEGIN
DECLARE @val varchar(80)
SELECT @val = value FROM master.dbo.fn_split(@notes, ',') WHERE value LIKE '%' + @SearchFor + '%'
IF @val IS NOT NULL
	SET @val = LEFT(@val, CHARINDEX('-', @val)-1)

RETURN @val 
END
GO
-- Use it in a query
SELECT ProjectNo, pubs.dbo.ufn_GetVal01('Ashish', notes)
	FROM test01	

You'll have to adjust DB and table names.

>Hi All,
>
>I want to search a specific value and find out the number before – in a varchar column
>
>The values are :
>
>ProjectNo Notes
>12 1-Ashish,4-Jain,1-Keith
>44 4-ABC,6-Ashish,8-CCC,6-SSS
>45 5-AAA,8-SSS,8-Keith,11-Ashish
>
>
>I want to return the values
>1
>6
>11
>for different project number when I search for string “Ashish” in Notes column.
>
>Thanks
>…ashish