How to find number of times a values is repeated in a string in a column

Question

In SQL HANA, I need to find how many times a given word is repeated in a string column whose values are delimited by "," and output it as a separate column.

Example, the string column contains:

ZN,ZN,ZS,ZQ

Expected result for "ZN":

You should handle such things in your program logic and not in SQL — juergen d, Sep 03 '19 at 06:30
i don't have any program logic used, its completely data base work — john, Sep 03 '19 at 06:36
Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Simon Price, Sep 03 '19 at 07:00
You may need a split function, and apply a simple "count / group by" request on the result : for the split function, see https://blogs.sap.com/2019/02/10/split-string-into-multiple-rows-using-sql-in-sap-hana/ — Raphaël Althaus, Sep 03 '19 at 07:55

score 1 · Answer 1 · answered Sep 03 '19 at 08:14

You might find it acceptable to search only the string ZN by ignoring the fact that there's a comma.

You may count the number of occurrences of any substring by using the string function OCCURRENCES_REGEXPR:

SELECT OCCURRENCES_REGEXPR('(ZN)' IN STRINGCOLUMN) "occurrences_zn" FROM TABLE;

If you really want to clearly specify that ZN is to be searched as an entire word between commas or at the edges, then you may find a better regular expression (the question is then more about regular expressions and not SQL HANA, and you may find existing answers in Stack Overflow).

score 0 · Answer 2 · answered Sep 03 '19 at 08:49

I can't remember where I found the trick, but in SQL Server, the following works like a charm:

DECLARE @myStringToSearch nvarchar(250) = 'ZN,ZN,ZS,ZQ'
DECLARE @searchValue nvarchar(5) = 'ZN'

SELECT (LEN(@myStringToSearch) - LEN(REPLACE(@myStringToSearch, @searchValue, ''))) / LEN(@searchValue)

The last line compares the length of the original string with the length of the same string, but this time replacing your search value (ZN) with a blank string. In our case, this would result in 4, because ZN is 2 characters, and it was removed twice. However, we're not interested in how many characters were removed, but in how many times the value was encountered, so we divide that result by the length of your search string (2).

Output of the query:

You could easily implement this as a DEFAULT constraint in your table, provided your search string is the same across every row.

score -1 · Answer 3 · answered Sep 10 '19 at 06:18

I wrote one anonymous block in sql , which can be converted to HANA Table function and can be used to achieve expected result.

DO

BEGIN

DECLARE FULL_STRING VARCHAR(100);

DECLARE TRIM_STRING VARCHAR(100);

DECLARE VAL_STRING VARCHAR(100);

FULL_STRING ='ZN,ZN,ZS,ZQ';

FULL_STRING=CONCAT(FULL_STRING,',');

--SELECT :FULL_STRING FROM DUMMY;

VAL_STRING=SUBSTRING(:FULL_STRING,1,LOCATE(:FULL_STRING,',',1)-1);

VAR_TABLE=SELECT :VAL_STRING STRINGVAL FROM DUMMY;

TRIM_STRING=SUBSTRING(:FULL_STRING,LOCATE(:FULL_STRING,',',1)+1 ,LENGTH(:FULL_STRING));

--SELECT * FROM :VAR_TABLE;

--SELECT :TRIM_STRING FROM DUMMY;

WHILE :TRIM_STRING IS NOT NULL AND LENGTH(:TRIM_STRING)>0

DO VAL_STRING=SUBSTRING(:TRIM_STRING,1,LOCATE(:TRIM_STRING,',',1)-1);

--SELECT :VAL_STRING FROM DUMMY;

VAR_TABLE=SELECT STRINGVAL FROM :VAR_TABLE UNION ALL SELECT :VAL_STRING FROM DUMMY;

TRIM_STRING=SUBSTRING(:TRIM_STRING,LOCATE(:TRIM_STRING,',',1)+1 ,LENGTH(:TRIM_STRING)); --i=i+1;

--SELECT :TRIM_STRING FROM DUMMY;

END WHILE ;

SELECT STRINGVAL,COUNT(STRINGVAL) FROM :VAR_TABLE GROUP BY STRINGVAL;

--SELECT :TRIM_STRING FROM DUMMY;

How to find number of times a values is repeated in a string in a column

3 Answers3