· 8 years ago · Jan 14, 2018, 12:16 AM
13333333333 -> returns 1
21113333333 -> returns 2
31112222444 -> returns 3
4
5mysql> select * from test ;
6+------------+
7| val |
8+------------+
9| 11111111 |
10| 111222222 |
11| 1113333222 |
12+------------+
13
14
15select
16val,
17sum(case when locate('1',val) > 0 then 1 else 0 end )
18+ sum( case when locate('2',val) > 0 then 1 else 0 end)
19+ sum(case when locate('3',val) > 0 then 1 else 0 end)
20+sum(case when locate('4',val) > 0 then 1 else 0 end ) as occurence
21from test group by val
22
23
24+------------+-----------+
25| val | occurence |
26+------------+-----------+
27| 11111111 | 1 |
28| 111222222 | 2 |
29| 1113333222 | 3 |
30+------------+-----------+
31
32mysql> select * from test ;
33+------------+
34| val |
35+------------+
36| 11111111 |
37| 111222222 |
38| 1113333222 |
39+------------+
403 rows in set (0.00 sec)
41
42mysql> select * from look_up ;
43+------+------+
44| id | val |
45+------+------+
46| 1 | 1 |
47| 2 | 2 |
48| 3 | 3 |
49| 4 | 4 |
50+------+------+
514 rows in set (0.00 sec)
52
53select
54t1.val,
55sum(case when locate(t2.val,t1.val) > 0 then 1 else 0 end ) as occ
56from test t1,(select * from look_up)t2
57group by t1.val ;
58
59+------------+------+
60| val | occ |
61+------------+------+
62| 11111111 | 1 |
63| 111222222 | 2 |
64| 1113333222 | 3 |
65+------------+------+
66
67SET @word='Hello World';
68
69SELECT charAtIdx, COUNT(charAtIdx)
70FROM (SELECT charIdx.id,
71 MID(@word, charIdx.id, 1) AS charAtIdx
72 FROM integerseries AS charIdx
73 WHERE charIdx.id <= LENGTH(@word)
74 ORDER BY charIdx.id ASC
75 ) wordLetters
76GROUP BY
77 wordLetters.charAtIdx
78ORDER BY charAtIdx ASC
79
80charAtIdx count(charAtIdx)
81--------- ------------------
82 1
83d 1
84e 1
85H 1
86l 3
87o 2
88r 1
89W 1
90
91SELECT
92 wordLetterCounts.wordId,
93 wordLetterCounts.word,
94 COUNT(wordLetterCounts.wordId) AS letterCount
95FROM
96 (SELECT words.id AS wordId,
97 words.word AS word,
98 iseq.id AS charPos,
99 MID(words.word, iseq.id, 1) AS charAtPos,
100 COUNT(MID(words.word, iseq.id, 1)) AS charAtPosCount
101 FROM
102 words
103 JOIN integerseries AS iseq
104 ON iseq.id BETWEEN 1 AND words.wordlen
105 GROUP BY
106 words.id,
107 MID(words.word, iseq.id, 1)
108 ) AS wordLetterCounts
109GROUP BY
110 wordLetterCounts.wordId
111
112wordId word letterCount
113------ -------------------- -------------
114 1 3333333333 1
115 2 1113333333 2
116 3 1112222444 3
117 4 Hello World 8
118 5 funny - not so much? 13
119
120CREATE TABLE `words` (
121 `id` int(11) NOT NULL AUTO_INCREMENT,
122 `word` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
123 `wordlen` int(11) NOT NULL,
124 PRIMARY KEY (`id`)
125) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
126
127/*Data for the table `words` */
128
129insert into `words`(`id`,`word`,`wordlen`) values (1,'3333333333',10);
130insert into `words`(`id`,`word`,`wordlen`) values (2,'1113333333',10);
131insert into `words`(`id`,`word`,`wordlen`) values (3,'1112222444',10);
132insert into `words`(`id`,`word`,`wordlen`) values (4,'Hello World',11);
133insert into `words`(`id`,`word`,`wordlen`) values (5,'funny - not so much?',20);
134
135CREATE TABLE `integerseries` (
136 `id` int(11) unsigned NOT NULL,
137 PRIMARY KEY (`id`)
138) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
139
140mysql> select * from chars;
141+----+------+
142| id | c |
143+----+------+
144| 1 | 1 |
145| 2 | 2 |
146| 3 | 3 |
147| 4 | 4 |
148+----+------+
149
150mysql> select * from words;
151+----+-----------+
152| id | word |
153+----+-----------+
154| 1 | 111222333 |
155| 2 | 11111111 |
156| 3 | 2222111 |
157| 4 | 5555555 |
158+----+-----------+
159
160mysql> select word, count(c) from words w inner join chars c on locate(c.c, word) group by word;
161+-----------+----------+
162| word | count(c) |
163+-----------+----------+
164| 11111111 | 1 |
165| 111222333 | 3 |
166| 2222111 | 2 |
167+-----------+----------+
168
169SELECT
170 CASE WHEN yourcolumn LIKE '%1%' THEN 1 ELSE 0 END +
171 CASE WHEN yourcolumn LIKE '%2%' THEN 1 ELSE 0 END +
172 CASE WHEN yourcolumn LIKE '%3%' THEN 1 ELSE 0 END +
173 CASE WHEN yourcolumn LIKE '%4%' THEN 1 ELSE 0 END +
174 CASE WHEN yourcolumn LIKE '%5%' THEN 1 ELSE 0 END +
175 CASE WHEN yourcolumn LIKE '%6%' THEN 1 ELSE 0 END +
176 CASE WHEN yourcolumn LIKE '%7%' THEN 1 ELSE 0 END +
177 CASE WHEN yourcolumn LIKE '%8%' THEN 1 ELSE 0 END +
178 CASE WHEN yourcolumn LIKE '%9%' THEN 1 ELSE 0 END +
179 CASE WHEN yourcolumn LIKE '%0%' THEN 1 ELSE 0 END
180FROM yourtable
181
182DROP FUNCTION IF EXISTS test.count_chrs;
183CREATE DEFINER=`test`@`localhost` FUNCTION `count_chrs`(s CHAR(100)) RETURNS CHAR(4)
184 BEGIN
185 DECLARE string_length int(4);
186 DECLARE unique_string CHAR(100) DEFAULT "";
187 DECLARE count_unique int(4) DEFAULT 0;
188 DECLARE current_char int(4) DEFAULT 1;
189 SET string_length = CHAR_LENGTH(s);
190
191 WHILE current_char <= string_length DO
192 IF (!LOCATE(SUBSTR(s, current_char, 1), unique_string)) THEN
193 SET count_unique = count_unique + 1;
194 SET unique_string = CONCAT(unique_string, SUBSTR(s, current_char, 1));
195 END IF;
196
197 SET current_char = current_char + 1;
198 END WHILE;
199
200 RETURN count_unique;
201 END;
202
203DECLARE @str VARCHAR(1000)
204--SET @str = '333333' --want to come: 1
205--SET @str = '333dddd' --want to come: 2
206SET @str = '333ddddf' --want to come: 3
207
208DECLARE @a INT =1, @out INT =0
209
210WHILE(@a <= LEN(@str))
211BEGIN
212 IF(SUBSTRING(@str, @a, 1) = SUBSTRING(@str, @a + 1, 1))
213 set @out = @out + 0
214 ELSE
215 set @out = @out + 1
216 set @a = @a + 1
217END
218
219SELECT @out