Table of Contents
PostgreSQL xử lý chuỗi với xpath
Tôi có bảng thế này
Giờ tôi cần tách theo cấu trúc thế này
Với cột định nghĩa cần cực kỳ ngắn gọn, 1 cụm từ thôi càng tốt
Cột description trên kia thì lại quá dài
Tách chuỗi với xpath
Ví dụ
WITH x(col) AS (SELECT '<?xml version="1.0" ?><response><h1>abbatial</h1><h3><i>əbeiʃəl</i></h3><h2>tính từ</h2><ul><li>(thuộc) trưởng tu viện</li></ul></response>'::xml)
SELECT (xpath('.//h3/i/text()', col))::text AS status
FROM xGiờ cần biến html từ html thành xml
Tạo cột xml
-- test gép chuỗi ra cấu trúc xml
SELECT '<?xml version="1.0" ?><response>' || html || '</response>'
FROM av
LIMIT 10;-- Update cột xml
UPDATE av
SET xml = '<?xml version="1.0" ?><response>' || html || '</response>';
Tách giá trị với xpath
Lỗi xmlParseEntityRef: no name
Replace
&with%26
UPDATE av
SET xml = REPLACE(xml, '&', '%26' );https://www.postgresqltutorial.com/postgresql-string-functions/postgresql-replace/
Tách giá trị với xpath
-- get pronounce
SELECT (xpath('./h3/i/text()', xml::xml))::text AS status
FROM av
LIMIT 10;
-- get def
SELECT (xpath('./ul/li/text()', xml::xml))::text AS status
FROM av
LIMIT 10;
Get only text
SELECT (xpath('./ul/li/text()', xml::xml))[1]::text AS status
FROM av
LIMIT 10;
https://openequella.github.io/tutorials/reporting/postgres/QueryingXMLType.html
Update dữ liệu def_v1
Do một số giá trị chứa ký tự đặc biệt như
<>nên lỗi xml. Do vậy chạy update toàn bộ sẽ bị lỗi và thoát.
Vì vậy viết script php chạy loop, lỗi thì bỏ qua
$sql="SELECT id FROM av;";
$query=pg_query($dbcon,$sql);
while($kq=pg_fetch_assoc($query)){
//echo $kq['id'];
$sql1="UPDATE av SET def_v1 = (xpath('./ul/li/text()', xml::xml))[1] Where id = ".$kq['id'].";";
//echo $sql1;
//echo '<br>';
try {
$query2=pg_query($dbcon,$sql1);
}
catch(Exception $e){
echo $sql1;
echo '<br>';
}
}Chuẩn hóa lại dữ liệu
-- Ví dụ
SELECT split_part('ordno-#-orddt-#-ordamt', '-#-', 3);
-- Tach lay 1 dinh nghia
SELECT split_part(def_v1, ', ', 1)
FROM av
LIMIT 50;
UPDATE av
SET def_v2 = split_part(def_v1, ', ', 1);
-- Tach lay dinh nghia sau dau :
SELECT split_part(def_v2, ': ', 2)
FROM av
WHERE "def_v2" ILIKE '%: %' LIMIT 50;
-- Tach lay dinh nghia truoc dau (
-- pico giây (một phần triệu triệu (10 - 12) của một giây)
SELECT split_part(def_v2, ' (', 1)
FROM av
WHERE "def_v2" ILIKE '% (%'
LIMIT 50;
-- Tach lay dinh nghia sau dau )
-- (từ lóng) người nghiện rượu
SELECT split_part(def_v2, ') ', 2)
FROM av
WHERE "def_v2" ILIKE '%) %'
LIMIT 50;
-- Tach lay dinh nghia trước dấu ;
-- dẻo; dễ nặn
SELECT split_part(def_v2, '; ', 1)
FROM av
WHERE "def_v2" ILIKE '%; %'
LIMIT 50;
-- Tach lay dinh nghia trước dấu *
-- trước 13 tuổi* danh từ
SELECT split_part(def_v2, '* ', 1)
FROM av
WHERE "def_v2" ILIKE '%* %'
LIMIT 50;
UPDATE av
SET def_v2 = split_part(def_v2, '* ', 1)
WHERE "def_v2" ILIKE '%* %';
-- Tach lay dinh nghia con chua (
-- (từ cổ; (:
SELECT * FROM "av" WHERE "def_v2" ILIKE '%(%' LIMIT 50
UPDATE av
SET def_v2 = NULL
WHERE "def_v2" ILIKE '%(%';