CODEWITHSUNDEEP
t-sqlcountcpu-word
malymakar
malymakar

Reputation: 99

Counting words in column

i must get count number the tag 

<name></name>

in column.

<users><name>Tomek</name><name>Pawel</name><name>Krzysiek</name></users>

In this example data, queries should return 3.

Upvotes: 0

Views: 69

Answers (3)

Vasily
Vasily

Reputation: 5782

if you work with xml data then try this variant

DECLARE @XMLdata XML = N'<users><name>Tomek</name><name>Pawel</name><name>Krzysiek</name></users>'
SELECT  COUNT(*) 
FROM @XMLdata.nodes('/users/name') col ( name )

This variant can be usefull when data storaged like a string (varchar)

--create temp table for testing
IF OBJECT_ID('Tempdb..#Tags') IS NOT NULL 
    DROP TABLE #Tags
CREATE TABLE #Tags
    (
      SampleText VARCHAR(1000)
    )
INSERT  INTO #Tags
        ( SampleText )
VALUES  ( '<users><name>Tomek</name><name>Pawel</name><name>Krzysiek</name></users>' ),
        ( '<users><name>Somik</name><name>Pawel</name><name>Krzysiek</name></users>' ),
        ( '<users><name>Krolik</name><name>Pawel</name><name>Krzysiek</name></users>' ),
        ( '<users><name>Domik</name><name>Pawel</name><name>Krzysiek</name></users>' ),
        ( '<users><name>Zontik</name><name>Pawel</name><name>Krzysiek</name></users>' );
--------------------------------------------------------------------------------
--  recursive cte for split string
WITH    cte
          AS ( SELECT   n = 1
               UNION ALL
               SELECT   n + 1
               FROM     cte
               WHERE    n <= 1000
             )
--------------------------------------------------------------------------------
-- final query
        SELECT  COUNT(*) AS Cnt
        FROM    cte
                JOIN #Tags AS T ON n <= LEN(T.SampleText)
        WHERE   SUBSTRING(T.SampleText, n, 7) = '</name>'
OPTION  ( MAXRECURSION 1000 )

Upvotes: 0

SubqueryCrunch
SubqueryCrunch

Reputation: 1495

Dynamic sql solution:

DECLARE @Table TABLE (Names NVARCHAR(1100))
INSERT INTO @Table VALUES
    ('<users><name>Tomek</name><name>Pawel</name><name>Krzysiek</name></users>'),
    ('<users><name>Tomek</name><name>Pawel</name><name>Krzysiek</name></users>'),
    ('<users><name>Tomek</name><name>Pawel</name><name>Krzysiek</name></users>')

DECLARE @Sql NVARCHAR(MAX)
SET @Sql = ''

SELECT
    @Sql = @Sql +

    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(Names,'</name>',''' as Names UNION ALL ')
            ,'<name>','SELECT ''')
        ,'</users>','')
    ,'<users>','')+CHAR(10)
FROM @Table

SET @Sql = LEFT(@Sql,LEN(@Sql)-11) 
SET @Sql = 'SELECT COUNT(Names) AS Names FROM (' + @Sql + ') as AllNames'
EXEC(@Sql)

Upvotes: 0

Manmay
Manmay

Reputation: 844

Using XPath you can easily implement the logic.

Example XPath for your scenario : count(/users/name)

Result : 3

Test Here

Upvotes: 1

Related Questions