Post reply

Count of word inside cell

  • July 12, 2022 at 9:49 am

    #4062024

    Hello all,

    I am trying to find a query that bring me the count of the exactly word name: PageDefinition like on the example.

    Thanks a lot all and best regards,

    Gilad

    Attachments:
    You must be logged in to view attached files.

  • July 12, 2022 at 10:11 am

    #4062036

    Try this

     

    select ID,detail_form,
          (len(detail_form) - len(replace(replace(replace(detail_form,'<PageDefintion>','<>'),'<PageDefintion/>','</>'),'</PageDefintion>','</>'))) / len('PageDefintion') as PageDefintionCount
    from mytable

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • July 12, 2022 at 1:14 pm

    #4062128

    Tks alot!!!

     

  • July 12, 2022 at 2:34 pm

    #4062185

    You don't need all those REPLACE functions.

    You can just do

    DECLARE @Find VARCHAR(MAX)='PageDefintion'  

    SELECT 
    Detail_Form
    ,LEN(@Find) ,(LEN(Detail_Form)-LEN(REPLACE(Detail_Form,@Find,'')))/LEN(@Find)  AS OccurrenceCount  
    FROM dbo.Table1

  • July 12, 2022 at 2:47 pm

    #4062186

    planetmatt wrote:

    You don't need all those REPLACE functions.

    You can just do

    DECLARE @Find VARCHAR(MAX)='PageDefintion'  

    SELECT 
    Detail_Form
    ,LEN(@Find) ,(LEN(Detail_Form)-LEN(REPLACE(Detail_Form,@Find,'')))/LEN(@Find)  AS OccurrenceCount  
    FROM dbo.Table1

     

    That will match PageDefintions and PageDefintion giving a count of 2 for ID=2

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • July 12, 2022 at 2:51 pm

    #4062187

    Mark Cowne wrote:

    That will match PageDefintions and PageDefintion giving a count of 2 for ID=2

    Then can't you just change the search string to:

    DECLARE @Find VARCHAR(MAX)='PageDefintion>'

    ?

  • July 12, 2022 at 3:47 pm

    #4062238

    planetmatt wrote:

    Mark Cowne wrote:

    That will match PageDefintions and PageDefintion giving a count of 2 for ID=2

    Then can't you just change the search string to:

    DECLARE @Find VARCHAR(MAX)='PageDefintion>'

    ?

    Yes you could, that would work for the data as provided but I was being cautious not wanting to match for example <AnotherPageDefintion>

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 7 posts - 1 through 6 (of 6 total)

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