github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_string_replace.result (about) 1 select replace("XYZ FGH XYZ","X","M"); 2 replace(XYZ FGH XYZ, X, M) 3 MYZ FGH MYZ 4 select replace(".*.*.*",".*","1"); 5 replace(.*.*.*, .*, 1) 6 111 7 select replace("11","","1"); 8 replace(11, , 1) 9 11 10 drop table if exists t1; 11 create table t1(a varchar,b varchar); 12 insert into t1 values("testtest","case case"); 13 select a,replace(b,"case","11") from t1; 14 a replace(b, case, 11) 15 testtest 11 11 16 select replace(a,"test","22") from t1; 17 replace(a, test, 22) 18 2222 19 select replace(a,"test",22),replace(b,"case","11") from t1; 20 replace(a, test, 22) replace(b, case, 11) 21 2222 11 11 22 SELECT REPLACE('aaaa','a','b'); 23 replace(aaaa, a, b) 24 bbbb 25 SELECT REPLACE('aaaa','aa','b'); 26 replace(aaaa, aa, b) 27 bb 28 SELECT REPLACE('aaaa','a','bb'); 29 replace(aaaa, a, bb) 30 bbbbbbbb 31 SELECT REPLACE('aaaa','','b'); 32 replace(aaaa, , b) 33 aaaa 34 SELECT REPLACE('bbbb','a','c'); 35 replace(bbbb, a, c) 36 bbbb 37 SELECT REPLACE(' hhdjs','','C'); 38 replace( hhdjs, , C) 39 hhdjs 40 SELECT REPLACE('absdefg','b',' '); 41 replace(absdefg, b, ) 42 a sdefg 43 SELECT REPLACE('rhjewnjfnkljvmkrewrjj','j',''); 44 replace(rhjewnjfnkljvmkrewrjj, j, ) 45 rhewnfnklvmkrewr 46 SELECT REPLACE('http://www.google.com.cn/','/',''); 47 replace(http://www.google.com.cn/, /, ) 48 http:www.google.com.cn 49 SELECT REPLACE('aaa.mysql.com','a','w'); 50 replace(aaa.mysql.com, a, w) 51 www.mysql.com 52 SELECT REPLACE('abchfjwabcABCvshfjdrvoiewjvkmdns mndsabcAbcueiuiwqjemkvwme000','abc','def'); 53 replace(abchfjwabcABCvshfjdrvoiewjvkmdns mndsabcAbcueiuiwqjemkvwme000, abc, def) 54 defhfjwdefABCvshfjdrvoiewjvkmdns mndsdefAbcueiuiwqjemkvwme000 55 SELECT REPLACE('dhnnnnjjkwkmoHskdhwjnejwqeHsk382983ndjhsemcsHskuwin mndsHsk','Hsk','t12vdjeke'); 56 replace(dhnnnnjjkwkmoHskdhwjnejwqeHsk382983ndjhsemcsHskuwin mndsHsk, Hsk, t12vdjeke) 57 dhnnnnjjkwkmot12vdjekedhwjnejwqet12vdjeke382983ndjhsemcst12vdjekeuwin mndst12vdjeke 58 SELECT REPLACE('jewTWUHWJWwwwwww', 'w', 'Q'); 59 replace(jewTWUHWJWwwwwww, w, Q) 60 jeQTWUHWJWQQQQQQ 61 SELECT REPLACE(' woshincjwne hjw ',' ','C'); 62 replace( woshincjwne hjw , , C) 63 CwoshincjwneChjwC 64 SELECT REPLACE(' 2617hejwnc 3920kdsm 3289uj ',' ','ssh'); 65 replace( 2617hejwnc 3920kdsm 3289uj , , ssh) 66 ssh2617hejwncssh3920kdsmssh3289ujssh 67 SELECT REPLACE(123456,6,1); 68 replace(123456, 6, 1) 69 123451 70 SELECT REPLACE(NULL,'', ''); 71 replace(null, , ) 72 null 73 SELECT REPLACE('', NULL, 3); 74 replace(, null, 3) 75 null 76 SELECT REPLACE(378273,2,NULL); 77 replace(378273, 2, null) 78 null 79 SELECT REPLACE('我是谁我在哪里', '我', '你'); 80 replace(我是谁我在哪里, 我, 你) 81 你是谁你在哪里 82 SELECT REPLACE('老师说我是好孩子', '你', '我'); 83 replace(老师说我是好孩子, 你, 我) 84 老师说我是好孩子 85 SELECT REPLACE(NULL, '哈', '哈'); 86 replace(null, 哈, 哈) 87 null 88 SELECT REPLACE('我是老大', '', ''); 89 replace(我是老大, , ) 90 我是老大 91 SELECT REPLACE('你好，见到你很高兴',NULL,'你'); 92 replace(你好，见到你很高兴, null, 你) 93 null 94 SELECT REPLACE('哈哈哈哈哈', '哈', NULL); 95 replace(哈哈哈哈哈, 哈, null) 96 null 97 SELECT REPLACE('今天是个好日子，明天也是个好日子，后天也是个好日子，大后天也是个好日子','后天','前天'); 98 replace(今天是个好日子，明天也是个好日子，后天也是个好日子，大后天也是个好日子, 后天, 前天) 99 今天是个好日子，明天也是个好日子，前天也是个好日子，大前天也是个好日子 100 SELECT REPLACE(' 老师说明天带我们去春游，后天带我们去植树 ,五一带我们去博物馆参观 ',' ','Alice'); 101 replace( 老师说明天带我们去春游，后天带我们去植树 ,五一带我们去博物馆参观 , , Alice) 102 Alice老师说明天带我们去春游，后天带我们去植树AliceAliceAlice,五一带我们去博物馆参观AliceAlice 103 SELECT REPLACE(' abc老师说我们班的语文成绩是全年级第1数学成绩是全年级第2', '全年级', '金台区'); 104 replace( abc老师说我们班的语文成绩是全年级第1数学成绩是全年级第2, 全年级, 金台区) 105 abc老师说我们班的语文成绩是金台区第1数学成绩是金台区第2 106 SELECT REPLACE( 'a', 'b', NULL ); 107 replace(a, b, null) 108 null 109 SELECT REPLACE( 'a', '', NULL ); 110 replace(a, , null) 111 null 112 SELECT REPLACE('','',NULL); 113 replace(, , null) 114 null 115 SELECT REPLACE( NULL, 'b', 'bravo' ); 116 replace(null, b, bravo) 117 null 118 SELECT REPLACE( NULL, '', 'bravo' ); 119 replace(null, , bravo) 120 null 121 SELECT REPLACE( 'a', NULL, 'bravo' ); 122 replace(a, null, bravo) 123 null 124 SELECT REPLACE( NULL, NULL, 'bravo' ); 125 replace(null, null, bravo) 126 null 127 SELECT REPLACE('37829((&^&8', '(', ')'); 128 replace(37829((&^&8, (, )) 129 37829))&^&8 130 SELECT REPLACE('dnu2@#$%^&(*()____**&^%', '*', ''); 131 replace(dnu2@#$%^&(*()____**&^%, *, ) 132 dnu2@#$%^&(()____&^% 133 SELECT REPLACE('7j^$&**JWI*@(@', '@', '@@@@@@') 134 SELECT REPLACE(NULL, 'a', '37*&'); 135 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 2 column 7 near " 136 SELECT REPLACE(NULL, 'a', '37*&');"; 137 SELECT REPLACE('45e&^%$', NULL, 'abc'); 138 replace(45e&^%$, null, abc) 139 null 140 SELECT REPLACE('782hfe4nijned,.,/.', '78', NULL); 141 replace(782hfe4nijned,.,/., 78, null) 142 null 143 SELECT REPLACE(NULL, NULL,'') / 2; 144 replace(null, null, ) / 2 145 null 146 DROP TABLE IF EXISTS replace_01; 147 CREATE TABLE replace_01( 148 s_id int(20) NOT NULL AUTO_INCREMENT, 149 s_name CHAR(5) DEFAULT NULL COMMENT '姓名', 150 phone VARCHAR(11) DEFAULT NULL COMMENT '电话', 151 PRIMARY KEY (s_id) 152 ); 153 INSERT INTO replace_01 VALUES (1, 'Tom', '13603735566'); 154 INSERT INTO replace_01 VALUES (2, 'Lee', '13603735533'); 155 INSERT INTO replace_01 VALUES (3, 'Harry', '13603735544'); 156 INSERT INTO replace_01 VALUES (4, 'Odin', '13603735577'); 157 INSERT INTO replace_01 VALUES (5, 'Jack', '13603735587'); 158 SELECT s_id, s_name, REPLACE(phone,'136','158') FROM replace_01; 159 s_id s_name replace(phone, 136, 158) 160 1 Tom 15803735566 161 2 Lee 15803735533 162 3 Harry 15803735544 163 4 Odin 15803735577 164 5 Jack 15803735587 165 SELECT * FROM replace_01 WHERE REPLACE(s_name, 'Tom', 'Bob') = 'Bob'; 166 s_id s_name phone 167 1 Tom 13603735566 168 SELECT REPLACE(s_name,'a','ahfjse') FROM replace_01; 169 replace(s_name, a, ahfjse) 170 Tom 171 Lee 172 Hahfjserry 173 Odin 174 Jahfjseck 175 SELECT s_id, s_name FROM replace_01 WHERE s_name = REPLACE('s_name','Jack','Bob'); 176 s_id s_name 177 SELECT REPLACE(phone, '136', NULL) FROM replace_01; 178 replace(phone, 136, null) 179 null 180 null 181 null 182 null 183 null 184 SELECT REPLACE(NULL, 'q','p') FROM replace_01; 185 replace(null, q, p) 186 null 187 null 188 null 189 null 190 null 191 SELECT REPLACE(s_name,'Tom','newqjndwdewmwve')FROM replace_01; 192 replace(s_name, Tom, newqjndwdewmwve) 193 newqjndwdewmwve 194 Lee 195 Harry 196 Odin 197 Jack 198 SELECT REPLACE(s_name, s_name, 'zhang') FROM replace_01; 199 replace(s_name, s_name, zhang) 200 zhang 201 zhang 202 zhang 203 zhang 204 zhang 205 INSERT INTO replace_01 VALUES(6, 'HHANjdncd','445478855'); 206 internal error: Can't cast 'HHANjdncd' to CHAR type. Src length 9 is larger than Dest length 5 207 INSERT INTO replace_01 VALUES(7, 'Wl', '11111124841550'); 208 internal error: Can't cast '11111124841550' to VARCHAR type. Src length 14 is larger than Dest length 11 209 UPDATE replace_01 set s_name = REPLACE(s_name,'Tom','efjhhoiwuwnvnjwiewori'); 210 internal error: Can't cast column from VARCHAR type to CHAR type because of one or more values in that column. Src length 21 is larger than Dest length 5 211 SELECT s_name, phone FROM replace_01 WHERE REPLACE(s_name, 'Tom', 'Bo') LIKE 'B%'; 212 s_name phone 213 Tom 13603735566 214 SELECT * FROM replace_01 WHERE REPLACE(s_name, 'Tom', 'Bo') NOT LIKE 'B%'; 215 s_id s_name phone 216 2 Lee 13603735533 217 3 Harry 13603735544 218 4 Odin 13603735577 219 5 Jack 13603735587 220 SELECT s_id, s_name FROM replace_01 WHERE REPLACE(s_id, 1, 6) BETWEEN 4 AND 7; 221 s_id s_name 222 1 Tom 223 4 Odin 224 5 Jack 225 SELECT * FROM replace_01 WHERE REPLACE(s_id, 2, 8) NOT BETWEEN 3 AND 6; 226 s_id s_name phone 227 1 Tom 13603735566 228 2 Lee 13603735533 229 SELECT * FROM replace_01 WHERE REPLACE('s_name','Lee',NULL) IS NULL; 230 s_id s_name phone 231 1 Tom 13603735566 232 2 Lee 13603735533 233 3 Harry 13603735544 234 4 Odin 13603735577 235 5 Jack 13603735587 236 SELECT * FROM replace_01 WHERE REPLACE('s_name','Lee','Vicky') IS NOT NULL; 237 s_id s_name phone 238 1 Tom 13603735566 239 2 Lee 13603735533 240 3 Harry 13603735544 241 4 Odin 13603735577 242 5 Jack 13603735587 243 SELECT * FROM replace_01 WHERE REPLACE(s_id, 1, 10) <4; 244 s_id s_name phone 245 2 Lee 13603735533 246 3 Harry 13603735544 247 SELECT s_id, s_name FROM replace_01 WHERE REPLACE(s_id, 2, 9) > 2; 248 s_id s_name 249 2 Lee 250 3 Harry 251 4 Odin 252 5 Jack 253 SELECT * FROM replace_01 WHERE REPLACE(s_id, 5, 9) >=5; 254 s_id s_name phone 255 5 Jack 13603735587 256 SELECT s_id, s_name FROM replace_01 WHERE REPLACE(s_id, 2, 9) != 6; 257 s_id s_name 258 1 Tom 259 2 Lee 260 3 Harry 261 4 Odin 262 5 Jack 263 SELECT * FROM replace_01 WHERE REPLACE(s_id, 5, 9) <=5; 264 s_id s_name phone 265 1 Tom 13603735566 266 2 Lee 13603735533 267 3 Harry 13603735544 268 4 Odin 13603735577 269 DROP TABLE IF EXISTS replace_02; 270 CREATE TABLE replace_02( 271 id int, 272 name VARCHAR(10), 273 password varchar(32), 274 mm bigint, 275 PRIMARY KEY(id) 276 ); 277 INSERT INTO replace_02 VALUES(1, ' 张三', '3672*(*^&hu32', 100); 278 INSERT INTO replace_02 VALUES(2, '李四 ', '4545dwjkekwe&&&&', 200); 279 INSERT INTO replace_02 VALUES(3, ' 王五 ', '&&%$%^&^*()))', 300); 280 INSERT INTO replace_02 VALUES(4, '赵六', 'ewu8qu39821dijoq', 400); 281 INSERT INTO replace_02 VALUES(5, '钱七', '--2102-9328', 500); 282 INSERT INTO replace_02 VALUES(6, ' ', ' ', 900); 283 UPDATE replace_02 set name = REPLACE(name,'张三','大哥'); 284 UPDATE replace_02 set password = REPLACE(password, '&&&&', 'AAAA'), name = REPLACE(name,'李四','小栗子'); 285 SELECT REPLACE(mm,mm,600) FROM replace_02; 286 replace(mm, mm, 600) 287 600 288 600 289 600 290 600 291 600 292 600 293 SELECT REPLACE(ltrim(name), '张三', '三张') FROM replace_02; 294 replace(ltrim(name), 张三, 三张) 295 大哥 296 小栗子 297 王五 298 赵六 299 钱七 300 301 SELECT REPLACE(rtrim(name), '李', '栗') FROM replace_02; 302 replace(rtrim(name), 李, 栗) 303 大哥 304 小栗子 305 王五 306 赵六 307 钱七 308 309 SELECT LENGTH(REPLACE(password, 'a', 'b')) FROM replace_02; 310 length(replace(password, a, b)) 311 13 312 16 313 13 314 16 315 11 316 1 317 SELECT ltrim(REPLACE('nhfej', 'fe', ' fe')); 318 ltrim(replace(nhfej, fe, fe)) 319 nh fej 320 SELECT rtrim(REPLACE('ejdwj 3782 ', '37', '2222')); 321 rtrim(replace(ejdwj 3782 , 37, 2222)) 322 ejdwj 222282 323 SELECT find_in_set('b',(REPLACE('a,b,c','a','b'))); 324 find_in_set(b, (replace(a,b,c, a, b))) 325 1 326 SELECT substring(REPLACE(name,'钱七','钱八'),0) FROM replace_02; 327 substring(replace(name, 钱七, 钱八), 0) 328 329 330 331 332 333 334 SELECT bin(REPLACE(id,1,4)) FROM replace_02; 335 bin(replace(id, 1, 4)) 336 100 337 10 338 11 339 100 340 101 341 110 342 SELECT hex(REPLACE(id, 2, 8)) FROM replace_02; 343 hex(replace(id, 2, 8)) 344 31 345 38 346 33 347 34 348 35 349 36 350 SELECT RPAD(REPLACE(password,'a','b'),40,'+-') FROM replace_02; 351 rpad(replace(password, a, b), 40, +-) 352 3672*(*^&hu32+-+-+-+-+-+-+-+-+-+-+-+-+-+ 353 4545dwjkekweAAAA+-+-+-+-+-+-+-+-+-+-+-+- 354 &&%$%^&^*()))+-+-+-+-+-+-+-+-+-+-+-+-+-+ 355 ewu8qu39821dijoq+-+-+-+-+-+-+-+-+-+-+-+- 356 --2102-9328+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ 357 +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ 358 SELECT REPLACE(id, 2, 10) AS newid FROM replace_02 ORDER BY newid DESC; 359 newid 360 6 361 5 362 4 363 3 364 10 365 1 366 DROP TABLE IF EXISTS replace_04; 367 CREATE TABLE replace_04( 368 id INT, 369 dd1 DATE, 370 dd2 DATETIME NOT NULL, 371 dd3 TIMESTAMP, 372 PRIMARY KEY (id) 373 ); 374 INSERT INTO replace_04 VALUES (1, '2020-01-01', '2020-01-01 12:12:12', '2020-02-02 06:06:06.163'); 375 INSERT INTO replace_04 VALUES (2, '2021-11-11', '2021-01-11 23:23:23', '2021-12-12 16:16:16.843'); 376 INSERT INTO replace_04 VALUES (3, '2002-11-11', '2002-01-11 23:23:23', '2002-12-12 16:16:16.843'); 377 INSERT INTO replace_04 VALUES (4, '2023-01-04', '1998-01-09 11:10:56', NULL); 378 SELECT * FROM replace_04 WHERE REPLACE(dd1,'20','21') < '2020-01-01'; 379 id dd1 dd2 dd3 380 SELECT dd3 FROM replace_04 WHERE REPLACE(dd1, '20', '21') > '2020-01-01'; 381 dd3 382 2020-02-02 06:06:06 383 2021-12-12 16:16:17 384 2002-12-12 16:16:17 385 null 386 SELECT MONTH(REPLACE(dd1, '20', '21')) FROM replace_04; 387 month(replace(dd1, 20, 21)) 388 1 389 11 390 11 391 1 392 SELECT year(REPLACE(dd2, 12, 20)) FROM replace_04; 393 year(replace(dd2, 12, 20)) 394 2020 395 2021 396 2002 397 1998 398 SELECT day(REPLACE(dd2, '23:23', '10:10')) , dd3 FROM replace_04; 399 day(replace(dd2, 23:23, 10:10)) dd3 400 1 2020-02-02 06:06:06 401 11 2021-12-12 16:16:17 402 11 2002-12-12 16:16:17 403 9 null 404 SELECT DATE_FORMAT(REPLACE(dd1, '20', '21'),'%m-%d-%Y') FROM replace_04; 405 date_format(replace(dd1, 20, 21), %m-%d-%Y) 406 01-01-2121 407 11-11-2121 408 11-11-2102 409 01-04-2123 410 SELECT DATE(REPLACE(dd2, 12, 20)) FROM replace_04; 411 date(replace(dd2, 12, 20)) 412 2020-01-01 413 2021-01-11 414 2002-01-11 415 1998-01-09 416 SELECT to_date(REPLACE(dd2, '23:23', '10:10'),'%Y-%m-%d %H:%i:%s') FROM replace_04; 417 to_date(replace(dd2, 23:23, 10:10), %Y-%m-%d %H:%i:%s) 418 2020-01-01 12:12:12 419 2021-01-11 10:10:23 420 2002-01-11 10:10:23 421 1998-01-09 11:10:56 422 SELECT weekday(REPLACE(dd2, 12, 20)) FROM replace_04; 423 weekday(replace(dd2, 12, 20)) 424 2 425 0 426 4 427 4 428 SELECT dayofyear(REPLACE(dd1, 20, 21)) FROM replace_04; 429 dayofyear(replace(dd1, 20, 21)) 430 1 431 315 432 315 433 4 434 SELECT id, extract(year FROM REPLACE(dd3, 20, 18)) FROM replace_04; 435 id extract(year, replace(dd3, 20, 18)) 436 1 1818 437 2 1821 438 3 1802 439 4 null 440 SELECT id, extract(MONTH FROM REPLACE(dd3, 20, 18)) FROM replace_04; 441 id extract(month, replace(dd3, 20, 18)) 442 1 02 443 2 12 444 3 12 445 4 null