{"id":95,"date":"2012-10-19T17:11:37","date_gmt":"2012-10-19T21:11:37","guid":{"rendered":"https:\/\/havalite.com\/?p=95"},"modified":"2022-11-22T17:24:59","modified_gmt":"2022-11-22T22:24:59","slug":"regexp-in-sqlite-using-php","status":"publish","type":"post","link":"https:\/\/havalite.com\/?p=95","title":{"rendered":"RegExp in SqLite using Php"},"content":{"rendered":"

One of the important thing that i kept messing as i switched from MySql to SqLite is the direct usage of regular expressions in sql statments. After searching many times in the internet for a solution, i only found out that SqLite doesn’t support RegExp by default except by changing the functionality of the database engine which depends on your running system.<\/p>\n

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message.<\/em><\/p><\/blockquote>\n

At stackoverflow.com<\/a> i found a discussion about this issue that didn’t help me much except this part here<\/p>\n

One basic requirement to invoke the function REGEXP in sqlite is
\n“You should create your own function in the application and then provide the callback link to the sqlite driver”<\/strong>.
\nFor that you have to use sqlite_create_function (C interface). You can find the detail from here<\/a> and here<\/a><\/p><\/blockquote>\n

I almost gave up und thought that the only possible way is to run all rows from myTable and check them one by one by calling the preg_match() function in php. But such a proccess will take to much time to complete. In this moment i was thinking how would php resolve this problem?<\/p>\n

According to php documents we can create or use existing function by using the SQLite3::createFunction<\/a>. Here we have a simple example:<\/p>\n

<?php\r\nfunction my_udf_md5($string) {\r\n    return md5($string);\r\n}\r\n\r\n$db = new SQLite3('mysqlitedb.db');\r\n$db->createFunction('my_udf_md5', 'my_udf_md5');\r\n\r\nvar_dump($db->querySingle('SELECT my_udf_md5(\"test\")'));\r\n\r\n\/\/ result:\r\n\/\/ string(32) \"098f6bcd4621d373cade4e832627b4f6\" \r\n?><\/pre>\n
In PDO its almost the same when using PDO::sqliteCreateFunction<\/a> which is still experimentell (see warnung) but works fine for our purpose. Here is the same example using PDO:<\/p>\n
<?php\r\n\r\nfunction my_udf_md5($string) {\r\n    return md5($string);\r\n}\r\n\r\n$db = new PDO('sqlite:mysqlitedb.db');\r\n$db->sqliteCreateFunction('mymd5', 'my_udf_md5', 1);\r\n\/\/ for the sqliteCreateFunction there are 3 parameters needed:\r\n\/\/ 1: function_name (any name you want)\r\n\/\/ 2: callback (the name of our function my_udf_mdf)\r\n\/\/ 3: Arguments (In this case our function has 1 argument)\r\n\r\n$rows = $db->query('SELECT mymd5(\"test\") FROM files')->fetchAll();\r\n\r\n?><\/pre>\n
Using preg_match function<\/h2>\n
You can copy the following sqlite_regExp()<\/span> function and try it by yourself. It works for me<\/p>\n
function sqlite_regExp($sql)\r\n{\r\n    $db = new PDO('sqlite:mysqlitedb.db');\r\n    if($db->sqliteCreateFunction(\"regexp\", \"preg_match\", 2) === FALSE) exit(\"Failed creating function!\");\r\n    if($res = $db->query($sql)->fetchAll()){ return $res; }\r\n    else return false;\r\n}\r\n\r\n\/\/ calling our function \/ sort matches\r\nif($rows = sqlite_regExp(\"SELECT * FROM myTable WHERE regexp('\/sql(ite)*\/ui', myRow)\")){\r\n    foreach($rows as $row) echo $row[0];\r\n}\r\n<\/pre>\n
That’s it!<\/p>\n
I believe that this methode is much effective way than using ready-made commands in a sql statement. It allows us to be more efficient in the way how we search in databases by using our self-made functions.<\/p>\n","protected":false},"excerpt":{"rendered":"
One of the important thing that i kept messing as i switched from MySql to SqLite is the direct usage of regular expressions in sql statments. After searching many times in the internet for a solution, i only found out that SqLite doesn’t support RegExp by default except by changing the functionality of the database […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[60],"tags":[65,53,64,59,62,63,61,66],"class_list":["post-95","post","type-post","status-publish","format-standard","hentry","category-tutorials","tag-createfunction","tag-database","tag-pdo","tag-php","tag-regex","tag-regular-expression","tag-sqlite","tag-sqlitecreatefunction"],"_links":{"self":[{"href":"https:\/\/havalite.com\/index.php?rest_route=\/wp\/v2\/posts\/95","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/havalite.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/havalite.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/havalite.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/havalite.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=95"}],"version-history":[{"count":2,"href":"https:\/\/havalite.com\/index.php?rest_route=\/wp\/v2\/posts\/95\/revisions"}],"predecessor-version":[{"id":451,"href":"https:\/\/havalite.com\/index.php?rest_route=\/wp\/v2\/posts\/95\/revisions\/451"}],"wp:attachment":[{"href":"https:\/\/havalite.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=95"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/havalite.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=95"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/havalite.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=95"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}