SQL query to get nth occurrence of underscore from a string

Question

I need to get a word from a string separated by underscores and inner join that with another table column to get unique data.

I have tried by creating a stored procedure and function,however it takes more than 4 minutes to get data, whereas if I do normal select query on table it takes 1 second, I guess my function does full table scan everytime.

Create PROCEDURE [dbo].[SP_GetTicketWeekly]
(   
    @P_skip int,
    @P_pageSize int,
    @P_Count int output 
)
AS
BEGIN   
    Select  d.TicketNumber as 'CouponID' , 
    SUBSTRING(CAST(d.TicketNumber AS varchar(38)), 11, 5) as 'PromoCode',  
    d.PromoStartDate as 'CouponStartDateID', 
    d.PromoEndDate as 'CouponEndDateID', 
    d.Description as 'CouponDesc',
    case 
    When d.PromoType in (0, 1) and dbo.ParsePart(d.Description, '_', 1) =a.value and (d.Description like 'ZV_%') 
    then a.description
    When d.PromoType = 1 and dbo.ParsePart(d.Description, '_', 1) =a.value
    then  ( CAST(d.PromoValue AS VARCHAR)+ '% ' + a.description )
    When d.PromoType = 0 and dbo.ParsePart(d.Description, '_', 1) = a.value then  ('$' + CAST(PromoValue AS VARCHAR)+' '+ a.description )
    end as 'Offer Description',
    SUBSTRING(CAST(TicketNumber AS varchar(38)), 8, 3) as 'Discount Amount', 
    d.PromoLevel as 'ItemTrans1', 
    --SUBSTRING(CAST(TicketNumber AS varchar(38)),5,1) as 'ItemTrans',
    d.PromoType as 'DollarPercent',
    --SUBSTRING(CAST(TicketNumber AS varchar(38)),7,1) as 'DollarPercent', 
    dbo.ParsePart(d.Description, '_', 2) as 'ChannelDesc',
    dbo.ParsePart(d.Description, '_', 3) as 'EventDesc' ,
    case 
    when dbo.ParsePart(d.Description, '_', 5) is NULL then
    dbo.ParsePart(d.Description, '_', 4)
    ELSE
    CONCAT(dbo.ParsePart(d.Description, '_', 4),'_',dbo.ParsePart(d.Description, '_', 5))
    END as 'AttDesc' 
    from [dbo].[Product] d
    inner join [dbo].[ProductDescription] a on dbo.ParsePart(d.Description, '_', 1) = a.value
    --where d.promoStartDate > 20140000 
    ORDER BY d.promoStartDate   OFFSET '' + @P_skip + '' ROWS Fetch NEXT '' + @P_pageSize + '' ROWS ONLY 
END

Create Function [dbo].[ParsePart]
    (
    @Data varchar(8000),
    @Delimiter VarChar(8000),
    @Index Int
    )
Returns VarChar(8000)
As
Begin
    Return (Select FieldName From dbo.Split(@Data, @Delimiter) Where Position = @Index)
End


Create Function [dbo].[Split]
    (
    @CommaDelimitedFieldNames Varchar(8000), 
    @CharToFind VarChar(10) 
    ) 
Returns @Tbl_FieldNames Table (Position Integer Identity(1,1), FieldName VarChar(8000)) As 
Begin 
 Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind
 Declare @Pos1 Int
 Declare @pos2 Int

 Set @Pos1=1
 Set @Pos2=1
 While @Pos1<DataLength(@CommaDelimitedFieldNames)
 Begin
 Set @Pos1 = CharIndex(@CharToFind, @CommaDelimitedFieldNames,@Pos1)
 Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(8000))
 Set @Pos2=@Pos1+1
 Set @Pos1 = @Pos1+1
 End 
 Return
End


TABLE 1

Column1   Column2
RSCL    off Reg Sale or Clearance Item  

TABLE2

Description              Column2
SA_ALL_PS_RSCL_CL        SAMPLE