dsafgdafgdf45345 2013-08-04 05:16
浏览 597
已采纳

regexp_replace中的转义字符串 - Postgresql和PHP

I'm importing a csv-file with user-data another person sent me. The file was sent back-and-forth between multiple people, working with different programs on different OS. So some encoding issues occurred and I found no easy way to fix it (yes I converted the file to UTF-8 etc).

The data are stored in a postgresql database and I found a way to convert the broken characters.

For example:
â\u0088\u009Aº = ü
â\u0088\u009A§ = ä
â\u0088\u009Aâ\u0088\u0082 = ö
...

In psql I'm using the following statement to convert â\u0088\u009Aº to ü:

SELECT COUNT(*) FROM sbl_students WHERE lastname ~* E'.*â\\u0088\\u009Aº.*';

=> 61

UPDATE sbl_students SET lastname = regexp_replace(lastname, E'â\\u0088\\u009Aº', 'ü', 'g');
SELECT COUNT(*) FROM sbl_students WHERE lastname ~* E'.*â\\u0088\\u009Aº.*';

=> 0

I tried to implement this into a php script, but for some reason it doesn't work. Nothing is replaced and no error occurs.

$dbh = pg_connect("dbname=iserv user=sbl");
$query = "UPDATE sbl_students SET lastname = regexp_replace(lastname, E'â\\u0088\\u009Aº', 'ü', 'g');";
pg_query($dbh, $query);
pg_close($dbh);

I already tried multiple variations like regexp_replace(lastname, E'â\u0088\u009Aº', 'ü', 'g') or regexp_replace(lastname, 'â\u0088\u009Aº', 'ü', 'g') but non of them worked.

Can anybody help me out? I don't understand why the query works great in psql but doesn't do anything in php.

Thanks!


Postgresql 8.4
PHP 5.3.3-7+squeeze16 with Suhosin-Patch (cli)

  • 写回答

2条回答 默认 最新

  • dongrouli2667 2013-08-04 06:51
    关注

    Found the answer myself. You have to use pg_prepare():

    $dbh = pg_connect("dbname=iserv user=sbl");
    pg_prepare($dbh, "", "UPDATE sbl_students SET lastname = regexp_replace(lastname, $1, 'ü', 'g');");
    pg_execute($dbh, "", array("â\u0088\u009Aº"));
    pg_close($dbh);
    

    Still don't understand why :/

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 代写uni代码,app唤醒
  • ¥15 全志t113i启动qt应用程序提示internal error
  • ¥15 ensp可以看看嘛.
  • ¥80 51单片机C语言代码解决单片机为AT89C52是清翔单片机
  • ¥60 优博讯DT50高通安卓11系统刷完机自动进去fastboot模式
  • ¥15 minist数字识别
  • ¥15 在安装gym库的pygame时遇到问题,不知道如何解决
  • ¥20 uniapp中的webview 使用的是本地的vue页面,在模拟器上显示无法打开
  • ¥15 网上下载的3DMAX模型,不显示贴图怎么办
  • ¥15 关于#stm32#的问题:寻找一块开发版,作为智能化割草机的控制模块和树莓派主板相连,要求:最低可控制 3 个电机(两个驱动电机,1 个割草电机),其次可以与树莓派主板相连电机照片如下:
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部