Post reply

How do I count the number of occurrences the character P in the string

  • April 16, 2018 at 11:06 am

    #337165

    Declare @STR as varchar(1000), @st varchar(8), @ed VARCHAR(8), @EarliestPrescriptionDt varchar(8)

    Select @STR =
    '<20170101><20171231><20170101>XPPPPXXXXXXXXXPPXXXXXXXXXPPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

    Select @st = SUBSTRING(@str,2,8)
    Select @ed = SUBSTRING(@str, 12, 8 )
    Select @EarliestPrescriptionDt = SUBSTRING(@str, 22, 8 )

    --Question: How do I cound the number of 'P' chars in the above string ?
    --Any idea if I could avoid a loop

  • April 16, 2018 at 11:32 am

    #1987031

    mw_sql_developer - Monday, April 16, 2018 11:06 AM

    Declare @STR as varchar(1000), @st varchar(8), @ed VARCHAR(8), @EarliestPrescriptionDt varchar(8)

    Select @STR =
    '<20170101><20171231><20170101>XPPPPXXXXXXXXXPPXXXXXXXXXPPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

    Select @st = SUBSTRING(@str,2,8)
    Select @ed = SUBSTRING(@str, 12, 8 )
    Select @EarliestPrescriptionDt = SUBSTRING(@str, 22, 8 )

    --Question: How do I cound the number of 'P' chars in the above string ?
    --Any idea if I could avoid a loop

    Here is a suggestion
    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;
    Declare @STR as varchar(1000), @st varchar(8), @ed VARCHAR(8), @EarliestPrescriptionDt varchar(8)
    Select @STR =
    '<20170101><20171231><20170101>XPPPPXXXXXXXXXPPXXXXXXXXXPPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
    Select @st = SUBSTRING(@str,2,8)
    Select @ed = SUBSTRING(@str, 12, 8 )
    Select @EarliestPrescriptionDt = SUBSTRING(@str, 22, 8 )
    --Question: How do I cound the number of 'P' chars in the above string ?
    --Any idea if I could avoid a loop
    SELECT
      NUM_P = LEN(@str) - LEN(REPLACE(@str,'P',''))

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply