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