Databases
StackOverflow2013
Postsdbo
POEscaped string in regexp_replace - Postgresql & PHP
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEscaped string in regexp_replace - Postgresql & PHP
    text
    copied!<p>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 <em>easy</em> way to fix it (yes I converted the file to UTF-8 etc).</p> <p>The data are stored in a postgresql database and I found a way to convert the broken characters.</p> <p>For example:<br> â\u0088\u009Aº = ü<br> â\u0088\u009A§ = ä<br> â\u0088\u009Aâ\u0088\u0082 = ö<br> ... </p> <p>In <strong>psql</strong> I'm using the following statement to convert <code>â\u0088\u009Aº</code> to <code>ü</code>:<br> </p> <pre class="lang-sql prettyprint-override"><code>SELECT COUNT(*) FROM sbl_students WHERE lastname ~* E'.*â\\u0088\\u009Aº.*'; </code></pre> <p>=> 61 </p> <pre class="lang-sql prettyprint-override"><code>UPDATE sbl_students SET lastname = regexp_replace(lastname, E'â\\u0088\\u009Aº', 'ü', 'g'); SELECT COUNT(*) FROM sbl_students WHERE lastname ~* E'.*â\\u0088\\u009Aº.*'; </code></pre> <p>=> 0</p> <p>I tried to implement this into a <strong>php</strong> script, but for some reason it doesn't work. <strong>Nothing is replaced and no error occurs.</strong> </p> <pre class="lang-php prettyprint-override"><code>$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); </code></pre> <p> I already tried multiple variations like <code>regexp_replace(lastname, E'â\u0088\u009Aº', 'ü', 'g')</code> or <code>regexp_replace(lastname, 'â\u0088\u009Aº', 'ü', 'g')</code> but non of them worked. </p> <p>Can anybody help me out? I don't understand why the query works great in psql but doesn't do anything in php. </p> <p>Thanks!</p> <hr> <p>Postgresql 8.4<br> PHP 5.3.3-7+squeeze16 with Suhosin-Patch (cli)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload