计算字符串中唯一字符的个数

本文介绍了计算字符串中唯一字符的个数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在寻找一个sql语句来计算字符串中的唯一字符数。

I'm looking for a sql statement to count the number of unique characters in a string.

例如

3333333333 -> returns 1 1113333333 -> returns 2 1112222444 -> returns 3

我使用REGEX和mysql-string函数做了一些测试，但我没有找到解决方案。

I did some tests with REGEX and mysql-string-functions, but I didn't find a solution.

推荐答案

没有直接或简单的方法。您可能需要编写一个存储函数来完成这项工作，并查看数据中可能出现的所有字符。这里是一个只有数字的例子，可以扩展存储函数中的所有字符。

There is no direct or easy way of doing it. You may need to write a store function to do the job and by looking at all the characters you may expect in the data. Here is an example for just digits , which could be extended for all the characters in a stored function

mysql> select * from test ; +------------+ | val | +------------+ | 11111111 | | 111222222 | | 1113333222 | +------------+ select val, sum(case when locate('1',val) > 0 then 1 else 0 end ) + sum( case when locate('2',val) > 0 then 1 else 0 end) + sum(case when locate('3',val) > 0 then 1 else 0 end) +sum(case when locate('4',val) > 0 then 1 else 0 end ) as occurence from test group by val +------------+-----------+ | val | occurence | +------------+-----------+ | 11111111 | 1 | | 111222222 | 2 | | 1113333222 | 3 | +------------+-----------+

或者如果你有足够的时间，创建一个查找表，包含你可以想到的所有字符。并在2行中进行查询

Or if you have enough time , create a lookup table with all the characters you could think of. And make the query in 2 lines

mysql> select * from test ; +------------+ | val | +------------+ | 11111111 | | 111222222 | | 1113333222 | +------------+ 3 rows in set (0.00 sec) mysql> select * from look_up ; +------+------+ | id | val | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +------+------+ 4 rows in set (0.00 sec) select t1.val, sum(case when locate(t2.val,t1.val) > 0 then 1 else 0 end ) as occ from test t1,(select * from look_up)t2 group by t1.val ; +------------+------+ | val | occ | +------------+------+ | 11111111 | 1 | | 111222222 | 2 | | 1113333222 | 3 | +------------+------+