I'm not sure that you need a function.

Here's an example using the over clause to solve the problem and at the same time the iTVF option. I strongly suggest you to read the article that I recommended to understand how scalar UDFs affect performance.

Note how I posted the sample data. You're expected to do it that way to get better help (and you should know it after all your visits).

CREATE TABLE dbo.Wells(

ID_Wells int,

Well_Name varchar(50))

INSERT INTO dbo.Wells

VALUES

(1, 'Something'),

(1, 'Something with Unit'),

(1, 'Something with another Unit'),

(2, 'One Unit Well'),

(2, 'Group Well'),

(3, 'Last Well')

GO

CREATE FUNCTION dbo.tRule_DoesWellNameContainUNIT(@ID_Wells int)

RETURNS table

AS

RETURN

SELECT Result = SIGN( COUNT(*) )

FROM Wells

WHERE (ID_Wells = @ID_Wells)

AND (Well_Name LIKE N'%Unit%');

GO

SELECT ID_Wells,

Well_Name,

SIGN( COUNT(CASE WHEN Well_Name LIKE '%Unit%' THEN 1 END) OVER( PARTITION BY ID_Wells)) AS Fed_Requirements,

r.Result AS Fed_RequirementsFunction

FROM dbo.Wells

CROSS APPLY dbo.tRule_DoesWellNameContainUNIT( ID_Wells) r

GO

DROP TABLE Wells