Stored Procedure:How to create split function and call it from Stored Procedure

I have two tables
CREATE TABLE [Admin].[study_centre](
[study_centre_id] [int] IDENTITY(1,1) NOT NULL,
[study_centre_name] nvarchar NOT NULL,
[state_id] [int] NOT NULL,
[programme] nvarchar NULL,
[study_centre_status] [int] NOT NULL

I want to create a function from study_centre table that will display the fields study_centre_id, study_centre_name, state_id and programme. But it will split field programme and remove the ','. Then, create a stored procedure from the table programme(fields are programme_id, programme_code, programme_name) with a condition where programme_code = programme (call the function and equate the result of the split column) .

How do I achieve this

This should get you started:

select *  /* specify the fields you want to see; don't use "select *" */
  from admin.study_centre as a
       cross apply string_split(a.programme,',') as b
       inner join programme as c
               on c.programme_code=b.value

Am using MSSQL 2014, and it does not recognise string_split()

I would recommend a new schema design if at all possible, if not do you want the results of the split as distinct rows or multi columns?

distinct row
study_centre_id ! programme
7 ! NCE
7 ! BDP
7 ! PGDE

one of the many of ways of doing it

--sample data not to be applied in your case but to emulate your data
create table #study_centre(
[study_centre_id] [int] NOT NULL,
[study_centre_name] nvarchar(150) NOT NULL,
[state_id] [int] NOT NULL,
[programme] nvarchar(150) NULL,
[study_centre_status] [int] NOT NULL

insert into #study_centre
select 7, 'Ngwa High School Aba + Ovuku/Omoba Sec. Sch', 4, 'NCE, BDP, PGDE', 0 union
select 7, 'Girls Technical College Ab', 4, 'NCE, BDP', 0 union
select 7, 'GSSS KWOI', 5, 'NCE, BDP', 0 union
select 7, 'Oxon Hill High School', 5, null, 0 

SELECT F1.[study_centre_id],
 cast('<X>'+replace(isnull(F.[programme],''),',','</X><X>')+'</X>' as XML) as xmlfilter 
from #study_centre F
 SELECT fdata.D.value('.','varchar(50)') as splitdata 
 FROM f1.xmlfilter.nodes('X') as fdata(D)) O

 drop table #study_centre

Then use DelimitedSplit8K by @JeffModen

Edit: Sorry @yosiasz. This should have been a reply to @noblemfd