如何在SQLite中查询列内任意位置含PUA字符的记录？

Got it, let's fix this problem right up. Your original query only catches rows where a PUA character is at the very start of the column, but you need to find any row that has a PUA character anywhere in the text. Here are a few solid approaches depending on your setup:

Option 1: Pure SQLite (No Extensions Needed) #

If you can't load extra extensions, a recursive CTE (Common Table Expression) will let you check every character in the column one by one. This works by breaking down each string into individual characters, checking if any fall into the Unicode PUA ranges, then matching back to the original row.

First, remember the three main Unicode PUA ranges we need to check:

• U+E000 to U+F8FF (Basic Multilingual Plane PUA)
• U+F0000 to U+FFFFD (Supplementary Multilingual Plane PUA)
• U+100000 to U+10FFFD (Supplementary Ideographic Plane PUA)

Here's the query:

WITH RECURSIVE char_check(pos, char_val, row_id) AS (
    -- Start with the first character of each row
    SELECT 1, substr(your_column, 1, 1), rowid
    FROM your_table
    UNION ALL
    -- Recursively get the next character until we reach the end of the string
    SELECT pos + 1, substr(your_column, pos + 1, 1), row_id
    FROM char_check
    JOIN your_table ON your_table.rowid = char_check.row_id
    WHERE pos <= length(your_column)
)
-- Get distinct rows where any character is in a PUA range
SELECT DISTINCT t.*
FROM your_table t
JOIN char_check cc ON t.rowid = cc.row_id
WHERE
    (unicode(cc.char_val) BETWEEN 0xE000 AND 0xF8FF)
    OR (unicode(cc.char_val) BETWEEN 0xF0000 AND 0xFFFFD)
    OR (unicode(cc.char_val) BETWEEN 0x100000 AND 0x10FFFD);

Just replace your_column and your_table with your actual column and table names. The rowid is SQLite's built-in primary key (if you didn't define your own, this will work—if you have a custom primary key, use that instead of rowid).

Option 2: Use Regular Expressions (Requires SQLite Extension) #

If you can enable SQLite's PCRE (Perl Compatible Regular Expressions) extension, this becomes way simpler. The regex pattern will match any character in the PUA ranges directly.

First, load the extension (the exact command depends on your environment—for example, in the SQLite CLI it's .load libsqlite3-pcre). Then run this query:

SELECT * FROM your_table
WHERE your_column REGEXP '[\uE000-\uF8FF\U000F0000-\U000FFFFD\U00100000-\U0010FFFD]';

This is faster and cleaner than the recursive CTE, but it does require the extension to be available.

Option 3: Custom SQLite Function (For App Integration) #

If you're using SQLite in an application (like Python, Java, etc.), you can create a custom SQL function that checks a string for PUA characters. For example, in Python with sqlite3:

import sqlite3

def has_pua_char(s):
    if not s:
        return False
    for c in s:
        code = ord(c)
        if (0xE000 <= code <= 0xF8FF) or (0xF0000 <= code <= 0xFFFFD) or (0x100000 <= code <= 0x10FFFD):
            return True
    return False

# Connect to your database
conn = sqlite3.connect('your_db.db')
conn.create_function('has_pua', 1, has_pua_char)

# Now run your query
cursor = conn.execute("SELECT * FROM your_table WHERE has_pua(your_column)")
results = cursor.fetchall()

This lets you use a clean, readable query and keeps the logic encapsulated in your code.

Why Your Original Query Didn't Work #

Just to clarify: your original query checked if the entire column value falls within a PUA range, which only happens when the string starts (and is entirely) PUA characters. By checking each individual character instead, we can catch PUA characters anywhere in the text.

内容的提问来源于stack exchange，提问作者Mou某