=Start=<\/p>\n\n\n\n
\u7b80\u5355\u8bb0\u5f55\u4e00\u4e0b\uff0c\u65b9\u4fbf\u540e\u9762\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\u3002<\/p>\n\n\n\n
\u601d\u8def\u6574\u7406\uff1a\n* \u5148\u5c06\u300c\u975e\u7279\u5b9a\u5b57\u7b26\u300d\u66ff\u6362\u6210\u300c\u7a7a\u300d\uff0c\u7136\u540e\u518d\u8ba1\u7b97\u5b57\u7b26\u4e32\u7684\u5269\u4f59\u957f\u5ea6\uff1a\n* \u5bf9\u4e8e\u5355\u4e2a\u5b57\u7b26\u6765\u8bf4\uff0c\u539f\u59cb\u5b57\u7b26\u4e32\u957f\u5ea6\u548c\u66ff\u6362\u540e\u7684\u5b57\u7b26\u4e32\u957f\u5ea6\u7684\u5dee\u503c\u5c31\u662f\u5355\u4e2a\u5b57\u7b26\u51fa\u73b0\u7684\u6b21\u6570\uff1b\n* \u5bf9\u4e8e\u5b50\u4e32\u6765\u8bf4\uff0c\u539f\u7406\u4e00\u6837\u53ea\u662f\u6700\u540e\u7528\u5dee\u503c\u9664\u4ee5\u5b50\u4e32\u7684\u957f\u5ea6\u5f97\u5230\u5b50\u4e32\u51fa\u73b0\u7684\u6b21\u6570\uff0c\u8fd8\u6709\u5c31\u662f\u5bf9\u4e8e Hive SQL \u6765\u8bf4\uff0c\u8ba1\u7b97\u5b57\u7b26\u4e32\u957f\u5ea6\u7684\u51fd\u6570\u662f length \u800c\u4e0d\u662f len\n\n# Counting the occurrences of a character\nSELECT\nLEN(summary) - LEN(REPLACE(summary, 'x', '')) AS occurrences\nFROM article\n\n# Counting the occurrences of a substring of any length\nIf we don\u2019t know the length of the substring, the expression to use is a bit more complex:\n\nSELECT\n(LEN(summary) - LEN(REPLACE(summary, 'France',''))) \/ LEN('France') AS occurrences\nFROM article\n\n# Case-sensitivity\nSingle character, case insensitive:\n\nSELECT\nLEN(summary) - LEN(REPLACE(LOWER(summary), LOWER('x'), '')) AS occurrences\nFROM article\n\nAny number of characters, case insensitive:\n\nSELECT\n(LEN(summary) - LEN(REPLACE(LOWER(summary), LOWER('France','')))) \/ LEN('France') AS occurrences\nFROM article\n\n# Performance\nIn the examples above, the expressions should be relatively fast (though, of course, it can be a problem when applied to a huge number of rows).\n\nNothing prevents us to use those expressions in a WHERE clause or in an ORDER BY clause, but this may heavily affect performance. The reason is that, if there is an index on the summary column, it cannot be used because the query optimizer cannot make assumptions on the results of the expressions.\n\nIf it is really necessary, this problem can be solved by building a computer column and an index on it, but this is out of the scope of this article.<\/code><\/pre>\n\n\n\nHive SQL\u4e2d\u7684\u5b57\u7b26\u4e32\u5904\u7406\u51fd\u6570\u8bf4\u660e\n\nreplace(string A, string OLD, string NEW)\n\nReturns the string A with all non-overlapping occurrences of OLD replaced with NEW (as of Hive 1.3.0 and 2.1.0).\nExample:\nselect replace(\"ababab\", \"abab\", \"Z\");\nreturns \"Zab\".\n\n==\n\nlength(string A)\n\nReturns the length of the string.<\/code><\/pre>\n\n\n\n\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n
SQL Server: How to count occurrences of a substring in a string
https:\/\/sql-bits.com\/sql-server-how-to-count-occurrences-of-a-substring-in-a-string\/<\/a><\/p>\n\n\n\nHive \u2013 Count number of occurences of character
https:\/\/stackoverflow.com\/questions\/36736022\/hive-count-number-of-occurences-of-character<\/a><\/p>\n\n\n\n