<\/span><\/h3>\n\n\n\ngbase> set @str='aBc'; select @str like '%b%', @str rlike 'b',regexp_like(@str,'b');\nQuery OK, 0 rows affected (Elapsed: 00:00:00.00)\n\n+-----------------+----------------+-----------------------+\n| @str like '%b%' | @str rlike 'b' | regexp_like(@str,'b') |\n+-----------------+----------------+-----------------------+\n| 1 | 1 | 0 |\n+-----------------+----------------+-----------------------+\n1 row in set (Elapsed: 00:00:00.00)\n\n\n<\/code><\/pre>\n\n\n\n<\/span>\u5b57\u6bb5<\/span><\/h3>\n\n\n\ngbase> select * from test1_n1 where address like '%d%';\r\n+------+----------------+-------------+--------------------------------------------+\r\n| id | name | address | note |\r\n+------+----------------+-------------+--------------------------------------------+\r\n| 1 | 123445 | address1 | 123445123445123445 |\r\n| 2 | 123445645656 | address2 | 123445645656123445645656123445645656 |\r\n| 3 | 12344564565633 | ad212dress2 | 123445645656331234456456563312344564565633 |\r\n+------+----------------+-------------+--------------------------------------------+\r\n3 rows in set (Elapsed: 00:00:00.00)\r\n\r\ngbase> select * from test1_n1 where address like '%D%';\r\nEmpty set (Elapsed: 00:00:00.00)\r\n\r\ngbase> select * from test1_n1 where address rlike 'd';\r\n+------+----------------+-------------+--------------------------------------------+\r\n| id | name | address | note |\r\n+------+----------------+-------------+--------------------------------------------+\r\n| 1 | 123445 | address1 | 123445123445123445 |\r\n| 2 | 123445645656 | address2 | 123445645656123445645656123445645656 |\r\n| 3 | 12344564565633 | ad212dress2 | 123445645656331234456456563312344564565633 |\r\n+------+----------------+-------------+--------------------------------------------+\r\n3 rows in set (Elapsed: 00:00:00.00)\r\n\r\ngbase> select * from test1_n1 where address rlike 'D';\r\n+------+----------------+-------------+--------------------------------------------+\r\n| id | name | address | note |\r\n+------+----------------+-------------+--------------------------------------------+\r\n| 1 | 123445 | address1 | 123445123445123445 |\r\n| 2 | 123445645656 | address2 | 123445645656123445645656123445645656 |\r\n| 3 | 12344564565633 | ad212dress2 | 123445645656331234456456563312344564565633 |\r\n+------+----------------+-------------+--------------------------------------------+\r\n3 rows in set (Elapsed: 00:00:00.00)\r\n<\/code><\/pre>\n\n\n\n\u5982\u679c\u8981like\u533a\u5206\uff0c\u53ef\u4ee5\u589e\u52a0binary\u5173\u952e\u5b57\uff0c\u6bd4\u5982<\/p>\n\n\n\n
gbase> set @str='aBc'; select @str like binary '%b%', @str rlike binary 'b',regexp_like(@str,'b');\nQuery OK, 0 rows affected (Elapsed: 00:00:00.00)\n\n+------------------------+-----------------------+-----------------------+\n| @str like binary '%b%' | @str rlike binary 'b' | regexp_like(@str,'b') |\n+------------------------+-----------------------+-----------------------+\n| 0 | 0 | 0 |\n+------------------------+-----------------------+-----------------------+\n1 row in set (Elapsed: 00:00:00.00)\n<\/code><\/pre>\n\n\n\n<\/span>like<\/span><\/h2>\n\n\n\n\u6700\u5e38\u7528\u7684\u4f7f\u7528%\u548c\u4e0b\u5212\u7ebf_\u8fdb\u884c\u6a21\u7cca\u5339\u914d\u7684\u5173\u952e\u5b57\u3002<\/p>\n\n\n\n
<\/span>\u8bed\u6cd5<\/span><\/h3>\n\n\n\nexpr LIKE pat [ESCAPE 'escape-char']\n<\/code><\/pre>\n\n\n\n<\/span>\u8bf4\u660e<\/span><\/h3>\n\n\n\n\u4f7f\u7528 SQL \u7684\u7b80\u5355\u7684\u6b63\u5219\u8868\u8fbe\u5f0f\u8fdb\u884c\u6bd4\u8f83\u7684\u6a21\u5f0f\u5339\u914d\u3002\u5982\u679c\u8868\u8fbe\u5f0f expr \u5339\u914d pat\uff0c\u8fd4\u56de 1\uff08TRUE\uff09\uff0c\u5426\u5219\u8fd4\u56de 0\uff08FALSE\uff09\u3002
\u6a21\u5f0f\u672a\u5fc5\u5c31\u662f\u6587\u5b57\u5b57\u7b26\u4e32\uff0c\u4f8b\u5982\uff0c\u5b83\u53ef\u4ee5\u4f7f\u7528\u5b57\u7b26\u4e32\u8868\u8fbe\u5f0f\u6216\u8868\u5217\u3002\u53ef\u4ee5\u5728\u6a21\u5f0f\u4e2d\u4f7f\u7528\u4e0b\u9762\u6240\u793a\u7684\u4e24\u4e2a\u901a\u914d\u7b26\u4e0e LIKE \u914d\u5408\u4f7f\u7528\u3002<\/p>\n\n\n\n
% \u5339\u914d\u4efb\u610f\u591a\u4e2a\u5b57\u7b26\uff0c\u6216\u96f6\u4e2a\u5b57\u7b26\u3002
_ \u4e25\u683c\u5730\u5339\u914d\u4e00\u4e2a\u5b57\u7b26\u3002<\/p>\n\n\n\n
\u4e00\u4e2alike\u91cc\u53ef\u4ee5\u7528\u591a\u4e2a\u5339\u914d\u7b26\u3002<\/p>\n\n\n\n
\\% \u5339\u914d%
\\_ \u5339\u914d\u4e0b\u5212\u7ebf_<\/p>\n\n\n\n
\u8f6c\u4e49\u7b26\u9ed8\u8ba4\u662f\u53f3\u659c\u6760\\,\u53ef\u4ee5\u7528ESCAPE\u53c2\u6570\u81ea\u5b9a\u4e49\u3002<\/p>\n\n\n\n
\u6ce8\u610f\uff1a\u7531\u4e8e GBase 8a MPP Cluster \u5728\u5b57\u7b26\u4e32\u4e2d\u4f7f\u7528 C \u8f6c\u4e49\u8bed\u6cd5(\u4f8b\u5982\uff0c\u7528\u201c\\n\u201d\u4ee3\u8868\u4e00\u4e2a\u6362\u884c\u5b57\u7b26)\uff0c\u5728 LIKE \u5b57\u7b26\u4e32\u4e2d\uff0c\u5fc5\u987b\u5c06\u7528\u5230\u7684\u201c\\\u201d\u53cc\u5199\uff1b
\u4f8b\u5982\uff0c\u82e5\u8981\u67e5\u627e\u201c\\n\u201d\uff0c\u5fc5\u987b\u5c06\u5176\u5199\u6210\u201c\\n\u201d\u3002\u800c\u82e5\u8981\u67e5\u627e\u201c\\\u201d\uff0c\u5219\u5fc5\u987b\u5c06\u5176\u5199\u6210\u2018\\\\\u2019\u3002\u539f\u56e0\u662f\u53cd\u659c\u7ebf\u7b26\u53f7\u4f1a\u88ab\u8bed\u6cd5\u5206\u6790\u7a0b\u5e8f\u5265\u79bb\u4e00\u6b21\uff0c\u5728\u8fdb\u884c\u6a21\u5f0f\u5339\u914d\u65f6\uff0c\u53c8\u4f1a\u88ab\u5265\u79bb\u4e00\u6b21\uff0c\u6700\u540e\u4f1a\u5269\u4e0b\u4e00\u4e2a\u53cd\u659c\u7ebf\u7b26\u53f7\u63a5\u53d7\u5339\u914d\u3002<\/p>\n\n\n\n
<\/span>\u6837\u4f8b<\/span><\/h3>\n\n\n\n<\/span>\u6b63\u5e38\u5339\u914d<\/span><\/h4>\n\n\n\ngbase> select 'abcdefg' like '%b_d%g';\n+-------------------------+\n| 'abcdefg' like '%b_d%g' |\n+-------------------------+\n| 1 |\n+-------------------------+\n1 row in set (Elapsed: 00:00:00.00)\n\ngbase> select * from t1;\n+------+--------+\n| id | name |\n+------+--------+\n| 1 | First |\n| 2 | Second |\n+------+--------+\n2 rows in set (Elapsed: 00:00:00.01)\n\ngbase> select * from t1 where name like 'F_r%t';\n+------+-------+\n| id | name |\n+------+-------+\n| 1 | First |\n+------+-------+\n1 row in set (Elapsed: 00:00:00.01)\n<\/code><\/pre>\n\n\n\n<\/span>\u8f6c\u4e49\u7b26\u5339\u914d%\u548c_<\/span><\/h4>\n\n\n\n\u901a\u8fc7\u8f6c\u4e49\u7b26\u8fdb\u884c\u5339\u914d\u3002<\/p>\n\n\n\n
gbase> select * from t1 where name like 'F%\\_%';\n+------+-------+\n| id | name |\n+------+-------+\n| 3 | Fo_ur |\n+------+-------+\n1 row in set (Elapsed: 00:00:00.01)\n<\/code><\/pre>\n\n\n\n<\/span>\u6307\u5b9a\u8f6c\u4e49\u7b26<\/span><\/h4>\n\n\n\n\u5982\u4e0b\u6307\u5b9a|\u4f5c\u4e3a\u8f6c\u4e49\u7b26\u3002<\/p>\n\n\n\n
gbase> select * from t1 where name like 'F%|_%' escape '|';\n+------+-------+\n| id | name |\n+------+-------+\n| 3 | Fo_ur |\n+------+-------+\n1 row in set (Elapsed: 00:00:00.01)\n<\/code><\/pre>\n\n\n\n<\/span>REGEXP\u3001RLIKE<\/span><\/h2>\n\n\n\n\u6b63\u5219\u5339\u914d\u7684\u5173\u952e\u5b57\u3002<\/p>\n\n\n\n
<\/span>\u8bed\u6cd5<\/span><\/h3>\n\n\n\nexpr REGEXP pat, expr RLIKE pat<\/code><\/pre>\n\n\n\n<\/span>\u8bf4\u660e<\/span><\/h3>\n\n\n\n\u5176\u4e2d\u7684pat\u662f\u6b63\u5219\u8868\u8fbe\u5f0f\u3002\u6ce8\u610f\u9ed8\u8ba4\u4e0d\u533a\u5206\u5927\u5c0f\u5199\u3002\u53ef\u4ee5\u7528regexp_like\u51fd\u6570\u6267\u884c\u5339\u914d\u539f\u5219\u3002<\/p>\n\n\n\n
<\/span>\u6837\u4f8b<\/span><\/h3>\n\n\n\n<\/span>\u666e\u901a\u5339\u914d<\/span><\/h4>\n\n\n\n\u5339\u914df\u5f00\u5934\uff0c\u540e\u9762\u4efb\u610f2\u4e2a\u5b57\u7b26\uff0cst\u7ed3\u5c3e\u7684\u3002 \u6ce8\u610fF\u548cf\u6ca1\u6709\u533a\u5206\u5927\u5c0f\u5199\u3002<\/p>\n\n\n\n
gbase> select 'First' regexp 'f.{2}st';\n+--------------------------+\n| 'First' regexp 'f.{2}st' |\n+--------------------------+\n| 1 |\n+--------------------------+\n1 row in set (Elapsed: 00:00:00.00)\n\ngbase> select 'First' regexp 'f.{3}st';\n+--------------------------+\n| 'First' regexp 'f.{3}st' |\n+--------------------------+\n| 0 |\n+--------------------------+\n1 row in set (Elapsed: 00:00:00.00)\n<\/code><\/pre>\n\n\n\n<\/span>\u5339\u914d136-139\u7684\u624b\u673a\u53f7<\/span><\/h4>\n\n\n\ngbase> select '13812345678' regexp '13[6-9][0-9]{8}';\n+----------------------------------------+\n| '13812345678' regexp '13[6-9][0-9]{8}' |\n+----------------------------------------+\n| 1 |\n+----------------------------------------+\n1 row in set (Elapsed: 00:00:00.00)\n<\/code><\/pre>\n\n\n\n<\/span>regexp_like()<\/span><\/h2>\n\n\n\n<\/span>\u8bed\u6cd5<\/span><\/h3>\n\n\n\nREGEXP_LIKE(source_char, pattern [, match_parameter]) <\/code><\/pre>\n\n\n\n<\/span>\u8bf4\u660e<\/span><\/h3>\n\n\n\n\u6a21\u7cca\u5339\u914d\u6307\u5b9a\u7684\u5b57\u7b26\u4e32\u3002<\/p>\n\n\n\n
source_char \u539f\u59cb\u5b57\u7b26\u4e32\u6216\u8005\u5b57\u6bb5\u3002
pattern \u6b63\u5219\u8868\u8fbe\u5f0f
match_parameter \u5339\u914d\u884c\u4e3a\u53c2\u6570\u3002<\/p>\n\n\n\n
\u53ef\u901a\u8fc7\u8bbe\u7f6e\u8be5\u53c2\u6570\u6539\u53d8\u9ed8\u8ba4\u7684\u5339\u914d\u529f\u80fd\u884c\u4e3a\u3002\u53ef\u4ee5\u662f\u5217\u540d\uff0c\u5217\u7684\u5185\u5bb9\u4e0d\u80fd\u8d85\u51fa\u8be5\u53c2\u6570\u7684\u503c\u57df\u8303\u56f4\u3002\u4f7f\u7528\u65f6\u8be5\u53c2\u6570\u7528\u5355\u5f15\u53f7\u5305\u56f4\uff0c\u4f8b\u5982\uff1a\u2019i\u2019\u3002<\/p>\n\n\n\n
\u9ed8\u8ba4\u548c\u8bbe\u7f6e\u6210 NULL \u60c5\u51b5\u4e0b\u201c.\u201d\u4e0d\u5339\u914d\u6362\u884c\u7b26\uff0c\u6e90\u5b57\u7b26\u4e32\u88ab\u770b\u4f5c\u4e00\u884c\u3002\u53c2\u6570\u53ef\u9009\u9879\u5982\u4e0b\uff1a
i\uff1a\u5927\u5c0f\u5199\u4e0d\u654f\u611f\uff1b
c\uff1a\u5927\u5c0f\u5199\u654f\u611f\uff1b
n\uff1a\u70b9\u53f7(.)\u5339\u914d\u6362\u884c\u7b26\u53f7\uff1b
m\uff1a\u591a\u884c\u6a21\u5f0f\uff1b
x\uff1a\u6269\u5c55\u6a21\u5f0f\uff0c\u5ffd\u7565\u6b63\u5219\u8868\u8fbe\u5f0f\u4e2d\u7684\u7a7a\u767d\u5b57\u7b26\u3002
\u7528\u6237\u540c\u65f6\u6307\u5b9a\u591a\u4e2a\u4e92\u65a5\u53c2\u6570\uff08i,c\uff09\u53ef\u9009\u9879\u65f6\uff0c\u7cfb\u7edf\u6309\u7167\u6700\u540e\u51fa\u73b0\u7684\u53c2\u6570\u5904\u7406\u3002<\/p>\n\n\n\n
\u9ed8\u8ba4\u5927\u5c0f\u5199\u654f\u611f\u3002<\/p>\n\n\n\n
<\/span>\u6837\u4f8b<\/span><\/h3>\n\n\n\n<\/span>\u666e\u901a\u5339\u914d<\/span><\/h4>\n\n\n\n\u6ce8\u610fF\u662f\u5927\u5c0f\u5199\u654f\u611f\u5339\u914d\u7684\u3002<\/p>\n\n\n\n
gbase> select regexp_like('First','F.{2}st');\n+--------------------------------+\n| regexp_like('First','F.{2}st') |\n+--------------------------------+\n| 1 |\n+--------------------------------+\n1 row in set (Elapsed: 00:00:00.00)\n\ngbase> select regexp_like('First','f.{2}st');\n+--------------------------------+\n| regexp_like('First','f.{2}st') |\n+--------------------------------+\n| 0 |\n+--------------------------------+\n1 row in set (Elapsed: 00:00:00.00)\n<\/code><\/pre>\n\n\n\n<\/span>\u5f3a\u5236\u5927\u5c0f\u5199\u4e0d\u654f\u611f<\/span><\/h4>\n\n\n\ngbase> select regexp_like('First','f.{2}st','i');\n+------------------------------------+\n| regexp_like('First','f.{2}st','i') |\n+------------------------------------+\n| 1 |\n+------------------------------------+\n1 row in set (Elapsed: 00:00:00.00)\n<\/code><\/pre>\n\n\n\n<\/span>\u5339\u914d\u6362\u884c\u7b26<\/span><\/h4>\n\n\n\ngbase> select regexp_like('Fir\\nst','f.{3}st','in');\n+---------------------------------------+\n| regexp_like('Fir\\nst','f.{3}st','in') |\n+---------------------------------------+\n| 1 |\n+---------------------------------------+\n1 row in set (Elapsed: 00:00:00.00)\n<\/code><\/pre>\n\n\n\n<\/span>regexp_replace()<\/span><\/h2>\n\n\n\n\u6b63\u5219\u66ff\u6362\u51fd\u6570<\/p>\n\n\n\n
<\/span>\u8bed\u6cd5<\/span><\/h3>\n\n\n\nregexp_replace(source_char,pattern[,replace_string[,position[,occurren\nce[match_option]]]])\n<\/code><\/pre>\n\n\n\n<\/span>\u8bf4\u660e<\/span><\/h3>\n\n\n\n\u7528 replace_string \u6307\u5b9a\u7684\u5b57\u7b26\u4e32\u66ff\u6362\u6e90\u5b57\u7b26\u4e32\u4e2d\u4e0e pattern \u6307\u5b9a\u7684\u6b63\u5219\u8868\u8fbe\u5f0f\u76f8\u5339\u914d\u7684\u5b57\u7b26\u4e32\u3002<\/p>\n\n\n\n
source_char
\u6e90\u5b57\u7b26\u4e32\u3002\u8be5\u53c2\u6570\u652f\u6301\u7684\u6570\u636e\u7c7b\u578b\u4e0e 8a \u7684 replace \u51fd\u6570\u7684 src \u53c2\u6570\u4e00\u81f4\u3002<\/p>\n\n\n\n
pattern
\u6b63\u5219\u8868\u8fbe\u5f0f\u3002\u6bcf\u4e2a\u6b63\u5219\u8868\u8fbe\u5f0f\u6700\u591a\u53ef\u5305\u542b 512 \u4e2a\u5b57\u8282\u3002\u5177\u4f53\u8bed\u6cd5\u89c4\u5219\u8bf7\u53c2\u8003 PCRE-7.8 \u7248\u672c\u7684\u8bed\u6cd5\u89c4\u5219\u8bf4\u660e<\/p>\n\n\n\n
replace_string
\u66ff\u6362\u5b57\u7b26\u4e32\u3002\u66ff\u6362\u5b57\u7b26\u4e32\u53ef\u4ee5\u5305\u542b\u53cd\u5411\u5f15\u7528\u7684\u6570\u5b57\u8868\u8fbe\u5f0f\uff08\\n\uff0cn \u7684\u53d6\u503c\u8303\u56f4\u662f[1,9]\uff09<\/p>\n\n\n\n
position
\u5f00\u59cb\u5339\u914d\u7684\u4f4d\u7f6e\uff0c\u5982\u679c\u4e0d\u6307\u5b9a\u9ed8\u8ba4\u4e3a 1\uff0c\u5373\u4ece source_char \u7684\u7b2c\u4e00\u4e2a\u5b57\u7b26\u5f00\u59cb\u5339\u914d\u3002position \u4e3a\u4e00\u4e2a\u6b63\u6574\u6570\u3002<\/p>\n\n\n\n
occurrence
\u6b63\u5219\u5339\u914d\u7684\u5e8f\u6570\u3002\u662f\u4e00\u4e2a\u975e\u8d1f\u7684\u6574\u6570\uff0c\u9ed8\u8ba4\u503c\u4e3a 0\u3002
\u6307\u5b9a\u4e3a 0\uff0c\u5219\u66ff\u6362\u6240\u6709\u5339\u914d\u5230\u7684\u5b57\u7b26\u4e32\uff1b
\u5982\u679c\u6307\u5b9a\u4e3a\u6574\u6570 n\uff0c\u5219\u66ff\u6362\u7b2c n \u6b21\u5339\u914d\u5230\u7684\u5b57\u7b26\u4e32\uff1b<\/p>\n\n\n\n
match_parameter
\u53ef\u901a\u8fc7\u8bbe\u7f6e\u8be5\u53c2\u6570\u6539\u53d8\u9ed8\u8ba4\u7684\u5339\u914d\u529f\u80fd\u884c\u4e3a\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u201c.\u201d\u4e0d\u5339\u914d\u6362\u884c\u7b26\uff0c\u6e90\u5b57\u7b26\u4e32\u88ab\u770b\u4f5c\u4e00\u884c\u3002\u53c2\u6570\u53ef\u9009\u9879\u5982\u4e0b\uff1a
i\uff1a\u5927\u5c0f\u5199\u4e0d\u654f\u611f\uff1b
c\uff1a\u5927\u5c0f\u5199\u654f\u611f\uff1b
n\uff1a\u70b9\u53f7(.)\u4e0d\u5339\u914d\u6362\u884c\u7b26\u53f7\uff1b
m\uff1a\u591a\u884c\u6a21\u5f0f\uff1b
x\uff1a\u6269\u5c55\u6a21\u5f0f\uff0c\u5ffd\u7565\u6b63\u5219\u8868\u8fbe\u5f0f\u4e2d\u7684\u7a7a\u767d\u5b57\u7b26\u3002<\/p>\n\n\n\n