SQL Server Function for Multivalued Attribute

Question

I have two tables in SQL Server.

SKILLS

Skill_ID   Skill_Title
-----------------------
1          Programming
2          Designing
3          Development

2nd table - EMPLOYEES

Emp_ID     Name           Skill_Title
--------------------------------------------------
1          Ali            Programming; Designing;
2          Ahmed          Designing; Development;
3          James          Development;

In the Employees table, each employee may have maximum number of any number of skills which are store by comma separated value.
To extract the first value form employee skills, i write query and it is working properly.

select name, skill_title, TRIM(SUBSTRING(e.[Skill_Title], 1, CHARINDEX(';', e.[Skill_Title]) - 1)) as first_skill
from EMPLOYEES e;

but when i try the function to use the skill_id from Skills tables based on matching, it is not displaying any value or null value Code for Function is as:

CREATE FUNCTION dbo.SkillID1 (@pty1 varchar)
RETURNS int
AS
  BEGIN
      RETURN
          (SELECT DISTINCT S.Skill_ID
           FROM [dbo].[Skills] s
           INNER JOIN [dbo].[Employees] e ON e.[Skill_Title] = TRIM(SUBSTRING(e.[Skill_Title], 1, CHARINDEX(';', e.[Skill_Title]) - 1))
           WHERE (TRIM(SUBSTRING(e.[Skill_Title], 1, CHARINDEX(';', e.[Skill_Title]) - 1))) = @pty1)
END
GO

I check this function like

SELECT 
    Emp_id, Name, Skill_Title, 
SkillID1(TRIM(SUBSTRING(e.[Skill_Title], 1, CHARINDEX(';', e.[Skill_Title]) - 1))) AS skill_id1 
FROM 
    Employees;

but it display null for skill_id1.

Please help me in this regards.

Answer 1

等待大神答复