github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_locate.result (about) 1 create database testx; 2 use testx; 3 SELECT LOCATE(null, "begtut.com", null) AS MatchPosition; 4 MatchPosition 5 null 6 SELECT LOCATE("com", "begtut.com", null) AS MatchPosition; 7 MatchPosition 8 null 9 SELECT LOCATE("com", null, 3) AS MatchPosition; 10 MatchPosition 11 null 12 SELECT LOCATE(NULL, null, 3) AS MatchPosition; 13 MatchPosition 14 null 15 SELECT LOCATE("com", "", 3) AS MatchPosition; 16 MatchPosition 17 0 18 SELECT LOCATE("", "cm", 3) AS MatchPosition; 19 MatchPosition 20 3 21 SELECT LOCATE("com", "begtut.com", 3) AS MatchPosition; 22 MatchPosition 23 8 24 SELECT LOCATE('lo', 'Hello, World!', 4) AS position; 25 position 26 4 27 SELECT LOCATE('lo', 'Hello, World!', 7) AS position; 28 position 29 0 30 SELECT LOCATE('is', 'This is a test string.', 6) AS position1, 31 LOCATE('is', 'This is a test string.', 10) AS position2; 32 position1 position2 33 6 0 34 DROP TABLE IF EXISTS test_table; 35 CREATE TABLE test_table ( 36 id INT AUTO_INCREMENT PRIMARY KEY, 37 my_string VARCHAR(200) 38 ); 39 INSERT INTO test_table (my_string) VALUES 40 ('Hello, World!'), 41 ('This is a test string.'), 42 ('Another string to test.'), 43 ('The quick brown fox jumps over the lazy dog.'), 44 ('Testing the LOCATE function.'), 45 ('12345'); 46 SELECT LOCATE('lo', my_string) AS position FROM test_table; 47 position 48 4 49 0 50 0 51 0 52 13 53 0 54 SELECT LOCATE('xyz', my_string) AS position FROM test_table; 55 position 56 0 57 0 58 0 59 0 60 0 61 0 62 SELECT LOCATE('is', my_string) AS position FROM test_table; 63 position 64 0 65 3 66 0 67 0 68 0 69 0 70 SELECT LOCATE('test', my_string) AS position FROM test_table; 71 position 72 0 73 11 74 19 75 0 76 1 77 0 78 DROP TABLE test_table; 79 DROP TABLE IF EXISTS mixed_language_table; 80 CREATE TABLE mixed_language_table ( 81 id INT AUTO_INCREMENT PRIMARY KEY, 82 text_data VARCHAR(255) 83 ); 84 INSERT INTO mixed_language_table (text_data) VALUES 85 ('applebanana苹果orange香蕉'), 86 ('grapepeach桃子mango'), 87 ('西瓜猕猴桃watermelonkiwistrawberry草莓'); 88 SELECT 89 id, 90 text_data, 91 LOCATE('orange', text_data) AS orange_position, 92 LOCATE('桃子', text_data) AS peach_position, 93 LOCATE('strawberry', text_data) AS strawberry_position 94 FROM 95 mixed_language_table; 96 id text_data orange_position peach_position strawberry_position 97 1 applebanana苹果orange香蕉 14 0 0 98 2 grapepeach桃子mango 0 11 0 99 3 西瓜猕猴桃watermelonkiwistrawberry草莓 0 0 20 100 SELECT 101 id, 102 text_data, 103 LOCATE('orange', text_data, 14) AS orange_position, 104 LOCATE('桃子', text_data, 14) AS peach_position, 105 LOCATE('strawberry', text_data, 14) AS strawberry_position 106 FROM 107 mixed_language_table; 108 id text_data orange_position peach_position strawberry_position 109 1 applebanana苹果orange香蕉 14 0 0 110 2 grapepeach桃子mango 0 0 0 111 3 西瓜猕猴桃watermelonkiwistrawberry草莓 0 0 20 112 SELECT 113 id, 114 text_data, 115 LOCATE('orange', text_data, 5) AS orange_position, 116 LOCATE('桃子', text_data, 5) AS peach_position, 117 LOCATE('strawberry', text_data, 5) AS strawberry_position 118 FROM 119 mixed_language_table; 120 id text_data orange_position peach_position strawberry_position 121 1 applebanana苹果orange香蕉 14 0 0 122 2 grapepeach桃子mango 0 11 0 123 3 西瓜猕猴桃watermelonkiwistrawberry草莓 0 0 20 124 DROP TABLE IF EXISTS mixed_language_table; 125 select locate('he','hello',-2); 126 locate(he, hello, -2) 127 0 128 select locate('lo','hello',-4294967295); 129 locate(lo, hello, -4294967295) 130 0 131 select locate('lo','hello',4294967295); 132 locate(lo, hello, 4294967295) 133 0 134 select locate('lo','hello',-4294967296); 135 locate(lo, hello, -4294967296) 136 0 137 select locate('lo','hello',4294967296); 138 locate(lo, hello, 4294967296) 139 0 140 select locate('lo','hello',-4294967297); 141 locate(lo, hello, -4294967297) 142 0 143 select locate('lo','hello',4294967297); 144 locate(lo, hello, 4294967297) 145 0 146 select locate('DB', 'ewhDBfjiejwew'); 147 locate(DB, ewhDBfjiejwew) 148 4 149 select locate('db', 'HHUHDNDBDBDBfewoiujfewqf vew'); 150 locate(db, HHUHDNDBDBDBfewoiujfewqf vew) 151 7 152 select locate("3q", 'fwfwefw3rqfw3fqff3qwfw'); 153 locate(3q, fwfwefw3rqfw3fqff3qwfw) 154 18 155 select locate("&&**", " ekwefw&&&**^^&3928u3f ") as result; 156 result 157 9 158 select locate(1,2); 159 locate(1, 2) 160 0 161 select locate(100, 2300100100); 162 locate(100, 2300100100) 163 5 164 select locate(-100, 3243211433100); 165 locate(-100, 3243211433100) 166 0 167 select locate('foo', null); 168 locate(foo, null) 169 null 170 select locate(null, 'o'); 171 locate(null, o) 172 null 173 select locate(null, null); 174 locate(null, null) 175 null 176 select locate('foo', null) is null; 177 locate(foo, null) is null 178 true 179 select locate(null, 'o') is null; 180 locate(null, o) is null 181 true 182 select locate(null, null) is null; 183 locate(null, null) is null 184 true 185 select isnull(locate('foo', null)); 186 isnull(locate(foo, null)) 187 true 188 select isnull(locate(null, 'o')); 189 isnull(locate(null, o)) 190 true 191 select isnull(locate(null, null)); 192 isnull(locate(null, null)) 193 true 194 select locate('lo','hello',3) as result; 195 result 196 4 197 select locate('he','hello',null),locate('he',null,2),locate(null,'hello',2); 198 locate(he, hello, null) locate(he, null, 2) locate(null, hello, 2) 199 null null null 200 select locate(null,'hello',null),locate('he',null,null); 201 locate(null, hello, null) locate(he, null, null) 202 null null 203 select locate('abc',ltrim(' ueenjfwabc123')); 204 locate(abc, ltrim( ueenjfwabc123)) 205 8 206 select locate('123',rtrim(' 3782dfw23123123123 ')); 207 locate(123, rtrim( 3782dfw23123123123 )) 208 12 209 select locate('%^',trim(' 32431 %^ 3829 3huicn2432g23 ')); 210 locate(%^, trim( 32431 %^ 3829 3huicn2432g23 )) 211 7 212 select locate('12',substring('21214avewwe12 ',3,20)); 213 locate(12, substring(21214avewwe12 , 3, 20)) 214 10 215 select locate('kjs', reverse('sjkdakjevenjwvev')) as result; 216 result 217 14 218 drop table if exists locate01; 219 create table locate01(col1 char(5), col2 varchar(20)); 220 insert into locate01 values ('da','database数据库DB数据库管理系统'); 221 insert into locate01 values (null,null); 222 insert into locate01 values ('ABCDF','GHIJKLMNabcdfeowejfwve'); 223 internal error: Can't cast 'GHIJKLMNabcdfeowejfwve' to VARCHAR type. Src length 22 is larger than Dest length 20 224 insert into locate01 values ('圣诞节快乐','圣诞节快乐Merry Charismas!'); 225 internal error: Can't cast '圣诞节快乐Merry Charismas!' to VARCHAR type. Src length 31 is larger than Dest length 20 226 insert into locate01 values ('@#$', '^&**(&^$@@#$'); 227 select * from locate01; 228 col1 col2 229 da database数据库DB数据库管理系统 230 null null 231 @#$ ^&**(&^$@@#$ 232 prepare s1 from 'select locate(col1, col2) from locate01'; 233 execute s1; 234 locate(col1, col2) 235 1 236 null 237 10 238 prepare s2 from 'select isnull(locate(col1, col2)) from locate01'; 239 execute s2; 240 isnull(locate(col1, col2)) 241 false 242 true 243 false 244 drop table locate01; 245 drop table if exists locate02; 246 create table locate02 (col1 tinytext, col2 text); 247 insert into locate02 values ('融合异构分布式','MatrixOne 是一款超融合异构分布式数据库，通过云原生化和存储、计算、事务分离的架构构建 HSTAP 超融合数据引擎，实现单一数据库系统支持 OLTP、OLAP、流计算等多种业务负载，并且支持公有云、私有云、边缘云部署和使用，实现异构基础设施的兼容。'); 248 insert into locate02 values (null, 'MatrixOne 具备实时 HTAP，多租户，流式计算，极致扩展性，高性价比，企业级高可用及 MySQL 高度兼容等重要特性，通过为用户提供一站式超融合数据解决方案，可以将过去由多个数据库完成的工作合并到一个数据库里，从而简化开发运维，消减数据碎片，提高开发敏捷度。'); 249 insert into locate02 values ('分布式', null); 250 select * from locate02; 251 col1 col2 252 融合异构分布式 MatrixOne 是一款超融合异构分布式数据库，通过云原生化和存储、计算、事务分离的架构构建 HSTAP 超融合数据引擎，实现单一数据库系统支持 OLTP、OLAP、流计算等多种业务负载，并且支持公有云、私有云、边缘云部署和使用，实现异构基础设施的兼容。 253 null MatrixOne 具备实时 HTAP，多租户，流式计算，极致扩展性，高性价比，企业级高可用及 MySQL 高度兼容等重要特性，通过为用户提供一站式超融合数据解决方案，可以将过去由多个数据库完成的工作合并到一个数据库里，从而简化开发运维，消减数据碎片，提高开发敏捷度。 254 分布式 null 255 select locate(col1, col2) from locate02; 256 locate(col1, col2) 257 15 258 null 259 null 260 select locate(col1, col2, 10) from locate02; 261 locate(col1, col2, 10) 262 15 263 null 264 null 265 select locate(col1, col2, -10) form locate02; 266 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 1 column 44 near " locate02;"; 267 drop table locate02; 268 drop table if exists locate03; 269 create temporary table locate03(col1 char(5), col2 mediumtext); 270 insert into locate03 values ('DB','database数据库DB数据库管理系统'); 271 insert into locate03 values (null,null); 272 insert into locate03 values ('ABCDF','GHIJKLMNabcdfeowejfwve'); 273 insert into locate03 values ('圣诞节快乐','圣诞节快乐Merry Charismas!'); 274 insert into locate03 values ('@#$', '^&**(&^$@@#$'); 275 select * from locate03; 276 col1 col2 277 DB database数据库DB数据库管理系统 278 null null 279 ABCDF GHIJKLMNabcdfeowejfwve 280 圣诞节快乐 圣诞节快乐Merry Charismas! 281 @#$ ^&**(&^$@@#$ 282 prepare s1 from 'select locate(col1, col2) from locate03'; 283 execute s1; 284 locate(col1, col2) 285 12 286 null 287 9 288 1 289 10 290 prepare s2 from 'select isnull(locate(col1, col2)) from locate03'; 291 execute s2; 292 isnull(locate(col1, col2)) 293 false 294 true 295 false 296 false 297 false 298 drop table locate03; 299 drop database testx;