将句子提取到单词的左边，但不包括以虚线作为整体词的单词。

Question

我试图把所有东西都提取到pin_delim的左边。这对于id 1,2,3,6很好，但是对于5. id 4不返回任何结果。我能想出如何阻止pin_delim在一个破折号-上分裂吗？

对于如何解决这个问题，以及为什么-被当作一个空间对待，有什么想法吗？

我总是希望左边是最后一个pin_delim字符串。

，美国特拉华州德佩雷大学，

返回特拉华州德佩雷大学，

不是“大学”

圣-文森特-德保罗，特拉华州，德，美国

返回圣文森特-德-保罗，特拉华州

不是“圣文森特-”

我试过以下几种方法

 WITH t2 AS (
    WITH t1 AS (
    SELECT 1 id,'middleDEword French,de, Polynesia.' pin_senetence,'de' pin_delimiter,'middleDEword French,' expected
    UNION ALL
    SELECT 2 id, 'Saint-Vincent-de-Paul,de,usa','de','Saint-Vincent-de-Paul,'
    UNION ALL
    SELECT 3 id,'HopiDEtal-de Saint Vincent de Paul,de,usa','de','HopiDEtal-de Saint Vincent de Paul,'
    UNION ALL
    SELECT 4 id,'middleDEword French, Polynesia.' pin_snetence,'de','middleDEword French, Polynesia.'
    UNION ALL
    SELECT 5 id,'Saint-Vincent-de-Paul,usa','de','Saint-Vincent-de-Paul,usa'
    UNION ALL
    SELECT 6 id,'HopiDEtal-de Saint DE Vincent de Paul,usa','de','HopiDEtal-de Saint DE Vincent ')
SELECT id,
    pin_senetence,
    pin_delimiter,
    expected,
    LENGTH(pin_delimiter) substr_sec_param,
    LENGTH(REGEXP_EXTRACT(pin_senetence, CONCAT( r'(?i)\b',  r'^(.*)', pin_delimiter,  r'\b.*'))) AS pos
    FROM t1
    WHERE 1 = 1
    AND REGEXP_CONTAINS(pin_senetence, CONCAT( r'(?i)\b',  r'^(.*)', pin_delimiter,  r'\b.*')))
    SELECT id,
    pin_senetence,
    SUBSTR(pin_senetence, pos + 1, substr_sec_param)          substr_val,
    SUBSTR(pin_senetence, 1, pos) actual,
    expected,
    IF(SUBSTR(pin_senetence, 1, pos) = expected, TRUE, FALSE) is_correct,
    pos,
    substr_sec_param
    FROM t2;


|id |pin_senetence                            |substr_val|actual                             |expected                           |is_correct|pos|substr_sec_param|
|---|-----------------------------------------|----------|-----------------------------------|-----------------------------------|----------|---|----------------|
|1  |middleDEword French,de, Polynesia.       |de        |middleDEword French,               |middleDEword French,               |TRUE      |20 |2               |
|2  |Saint-Vincent-de-Paul,de,usa             |de        |Saint-Vincent-de-Paul,             |Saint-Vincent-de-Paul,             |TRUE      |22 |2               |
|3  |HopiDEtal-de Saint Vincent de Paul,de,usa|de        |HopiDEtal-de Saint Vincent de Paul,|HopiDEtal-de Saint Vincent de Paul,|TRUE      |35 |2               |
|5  |Saint-Vincent-de-Paul,usa                |de        |Saint-Vincent-                     |Saint-Vincent-de-Paul,usa          |FALSE     |14 |2               |
|6  |HopiDEtal-de Saint DE Vincent de Paul,usa|de        |HopiDEtal-de Saint DE Vincent      |HopiDEtal-de Saint DE Vincent      |TRUE      |30 |2               |

Answer 1

我建议反转这两个字符串、文本和令牌pin_delimiter。在正则表达式中，我们可以搜索标记"ed“(反向"de")的第一次出现，并在此之后进行所有处理。反转此结果是字符串的开始，直到令牌的最后出现为止。

为了排除-作为令牌分隔符，请在[^\w\-]中排除这些分隔符。

    WITH t1 AS (
    SELECT 1 id,'middleDEword French,de, Polynesia.' pin_senetence,'de' pin_delimiter,'middleDEword French,' expected
    UNION ALL
    SELECT 2 id, 'Saint-Vincent-de-Paul,de,usa','de','Saint-Vincent-de-Paul,'
    UNION ALL
    SELECT 3 id,'HopiDEtal-de Saint Vincent de Paul,de,usa','de','HopiDEtal-de Saint Vincent de Paul,'
    UNION ALL
    SELECT 4 id,'middleDEword French, Polynesia.' pin_snetence,'de','middleDEword French, Polynesia.'
    UNION ALL
    SELECT 5 id,'Saint-Vincent-de-Paul,usa','de','Saint-Vincent-de-Paul,usa'
    UNION ALL
    SELECT 6 id,'HopiDEtal-de Saint DE Vincent de Paul,usa','de','HopiDEtal-de Saint DE Vincent ')
SELECT *,
#substring(pin_senetence,1,length(pin_senetence)-length(text_ending)+2) as extracted,
substring(text_max,1,length(text_max)-length(pin_delimiter)-1) as text_extracted,
substring(text_max,1,length(text_max)-length(pin_delimiter)-1)=expected
from(
Select *,
#REGEXP_EXTRACT(concat(pin_senetence," "),CONCAT(r"([^\w\-]",pin_delimiter,r"[^\w\-].*)")) as text_ending,
reverse(REGEXP_EXTRACT(reverse(concat(pin_senetence," ")),CONCAT(r"(?i)([^\w\-]",reverse(pin_delimiter),r"[^\w\-].*)"))) as text_max
from t1
)