TSQL Split function

A very efficient, probably "best practice", splitter is dbo.DelimitedSplit8K.  You can Google for the source.

not just splitting, I want the second string (added with first strings first 4 characters as shown in my example...)

Nah, still basically just a split, you just have to get the prefix first, then split the string minus the prefix, and add the prefix back to the front of every item afterward :) :

DECLARE @string varchar(8000)
SET @string = 'CNCS01306/01307/1309/241/99908765'

SELECT ca1.string_prefix + ds.Item
FROM (
    SELECT @string AS string
) AS base_data
CROSS APPLY (
    SELECT LEFT(string, PATINDEX('%[0-9]%', string) - 1) AS string_prefix
) AS ca1
CROSS APPLY dbo.DelimitedSplit8K (SUBSTRING(string, LEN(string_prefix) + 1, 8000), '/') ds

If the format and length remains same, why can't you use simple left function?

Left(columnname,9)

My bad, ignore my previous comment

check this. If this works for you, you can create a function and with this logic and call it in your code.

declare @str varchar(100) = 'CNCS01306/01307'

select case when n > 1 then left(string,4) else '' end + ltrim(SUBSTRING(string, n, CHARINDEX('/', string + '/',n) - n)) split_string
  from (select @str string) t1
  CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
 WHERE SUBSTRING('/' + string, n, 1) = '/'

Select allOpen in new window

@Author - Are you satisfied with the above solutions ? or you need some more info.

Closing since the author has not returned.