How often a string appears in a table in each record (SQL Server)

Asked

Viewed 213 times

-1

I have a table in SQL with a column called "content", varchar . This column stores the contents of Xmls.

I need to count the number of times a certain word (tag) is in each XML, ie for each record of this table.

Would anyone know how to help me? SOS

sql-server xml count

1 answer

1

Dude...I think the easiest way to do this is gonna be this one, yo:

DECLARE @VAR VARCHAR(50) =  'TAG'
SELECT (LEN(CAMPOXML) - LEN(REPLACE(CAMPOXML,@VAR,''))) / LEN(@VAR)

You take the field size

>> LEN(CAMPO)

then make a replace to take out the word you want to find and see what size it got:

>> LEN(REPLACE(CAMPO,@VAR,''))

subtracts to know the difference of characters:

>> (LEN(CAMPO) - LEN(REPLACE(CAMPO,@VAR,'')))

and finally you divide by the size of the variable (word you want to find), to return the amount.

  • Thank you! This LEN function, accepts as argument the name of a column of a view? It occurs that when I run , SQL tells me Invalid column name

  • Yes, it does. Edit the response and add an error print/check that the column name is actually correct.

  • 2

    How are you doing? About your question of why the question was closed, because, I believe, that the user did not provide any code but asked for a code, it would be like: 'e ai pedro, do a hello word for me in python? 'Of course for those who understand python is very simple to do, but this is not the purpose of the site, but rather helps it: 'Why couldn’t you do hello word? 'What doubt do you have? ' understood? Read these links: https://pt.meta.stackoverflow.com/questions/5483/manual-de-como-n%C3%83o-fazer-perguntas https://pt.meta.stackoverflow.com/a/6252/110782

  • I already delete the comment :D

  • I will not deny the answer because it is correct, but this seems to be a problem do for me where the academic is not in the mood to do his homework and put here in the expectation that someone does the lesson for him for free. To filter these cases of legitimate questions the community has instituted a [MCVE] filter also known as [MCVE], which is nothing more than a request for proof of the legitimacy of the question.

  • Another thing when you have this type of complaint. Do it in the relevant channel which is the META where the questions are about the functioning of the site, community questions, bugs, proposals for improvements and new features.

Show 1 more comment

Browser other questions tagged sql-server xml count

You are not signed in. Login or sign up in order to post.