在某个词之后删除所有文本

I have data in a database, the rows are like:

我在数据库中有数据，行是这样的:

Ticket #3532 Updated
Ticket #43252 Opened
Ticket #5462 Opened
Ticket #353 Updated

on each row, there is text after the word Updated or Opened

在每一行中，在单词更新或打开后都有文本

I would like to remove this text on every row so it just displays exactly like the above.

我想删除每一行上的这个文本，这样它就会像上面一样显示出来。

I have tried using:

我有试着用:

strstr($result["notes"], 'Updated', true)

For the Updated rows, but this returns

对于已更新的行，但是返回

Ticket #34564

and not the word updated

而不是更新的词

How can i execute this in either PHP or directly in SQL?

如何在PHP或SQL中直接执行?

6 个解决方案

#1

You can do the update directly in SQL using the LOCATE() function to find the first instance of Updated or Opened in the string, and the LEFT() string function to left-truncate it to that position plus the length of the word Updated or Opened. There would be a number of ways to handle this with string functions, but this is the first that comes to mind.

可以使用LOCATE()函数在SQL中直接进行更新，以查找字符串中第一个更新或打开的实例，然后使用LEFT()字符串函数将其截断到该位置，再加上更新或打开的单词的长度。有很多方法可以用字符串函数来处理这个问题，但这是我首先想到的。

UPDATE tickets
SET notes = LEFT(notes, LOCATE('Opened', notes) + LENGTH('Opened'))
WHERE notes LIKE '%Opened%';

UPDATE tickets
SET notes = LEFT(notes, LOCATE('Updated', notes) + LENGTH('Updated'))
WHERE notes LIKE '%Updated%';

Both LEFT() and LOCATE() are documented among the MySQL string functions.

LEFT()和LOCATE()都记录在MySQL字符串函数中。

Here is an example (as a SELECT)

这里有一个示例(作为SELECT)

And the same example reformulated for the UPDATE statements

同样的例子在更新语句中得到了重新表述

#2

Assuming you speak about MySql, you can select something like :

假设你说的是MySql，你可以选择:

SELECT CONCAT(SUBSTRING_INDEX(myColumn, 'Updated', 1), 'Updated')

with a where clause to handle only the rows having 'Updated'.

使用where子句只处理具有“更新”的行。

#3

PHP solution

PHP解决方案

$str = 'Ticket #3532 Updated some text after';
$needle = 'Updated';

$substr = substr($str, 0, strpos($str, $needle) + strlen($needle));

#4

Assuming you are using MySQL and you created a proper backup of your database, the following query should do the job:

假设您正在使用MySQL，并且您创建了数据库的适当备份，下面的查询应该可以完成以下任务:

UPDATE `yourtable`
SET `yourcolumn` = CONCAT(
    SUBSTRING_INDEX(
        CONCAT(SUBSTRING_INDEX(`yourcolumn`, 'Updated', 1), 'Updated'),
    'Opened', 1),
    'Opened');

#5

    <?php
    $string = "Ticket #3532 Updated
    Ticket #43252 Opened
    Ticket #5462 Opened
    Ticket #353 Updated";

    echo preg_replace('/(Updated|Opened)/', '', $string);

    //    OR

    $string2 = "Ticket #3532 Updated";

    echo preg_replace('/(Updated|Opened)/', '', $string2);

    ?>

#6

Personally, I think it's a bit kludgy to do this in MySQL since MySQL doesn't support REGEXP_REPLACE() or REGEXP_SUBSTR(). On the other hand, PHP has a good regex engine and should be able to grab a match fairly easily. A good regex to match might be:

我个人认为在MySQL中这样做有点笨拙，因为MySQL不支持REGEXP_REPLACE()或REGEXP_SUBSTR()。另一方面，PHP有一个很好的regex引擎，应该能够很容易地获取匹配。一个好的regex可能是:

/^(Ticket #\d+ (?:Updated|Opened)).*$/

And your code might look something like:

你的代码可能是这样的:

$new_notes = preg_replace('/^(Ticket #\d+ (?:Updated|Opened).*$/', '$1', $result["notes"]);

#1

UPDATE tickets
SET notes = LEFT(notes, LOCATE('Opened', notes) + LENGTH('Opened'))
WHERE notes LIKE '%Opened%';

UPDATE tickets
SET notes = LEFT(notes, LOCATE('Updated', notes) + LENGTH('Updated'))
WHERE notes LIKE '%Updated%';