MYSQL:选择文本的一部分，然后根据点的位置将其剪切 [英] MYSQL: select part of text and cut it of based on dot location

问题描述

如果我有一个这样的表:

If I had a table like this:

--------------------------
| id |      content      |
--------------------------
| 1  | text 1500 chars   |
--------------------------
| 2  | text 300 chars    |
--------------------------
| 3  | text 501 chars    |
--------------------------
| 4  | text 500 chars    |
-------------------------- 

是否可以创建一个查询，该查询最多选择大约500个字符并在点位置处删除字符串.

Is it possible to create a query which selects up to approximately 500 characters and cuts the sting at dot location.

示例:

Example:

Lorem Ipsum就是简单的... PageMaker(此处为500个字符)Lorem Ipsum. <-在切开.位置.

Lorem Ipsum is simply ... PageMaker (here at 500 chars) Lorem Ipsum. <- cut at . location.

这可能吗?如果没有，我将尝试创建PHP解决方案.目前，我正在研究一些MYSQL函数以完成此操作.如果我能在查询中实现这一目标，那就太好了.

Is this possible? If not I will try to create PHP solution. Currently I am looking into some MYSQL functions to get this done. It would be nice if I could achieve this within a query.

推荐答案

也许类似的方法可能有效:

Maybe something like this could work :

SELECT
    id,
    CASE
        WHEN len <= 500 THEN content
        ELSE CASE
            WHEN idx > 0 THEN SUBSTRING(content, 1, idx)
            ELSE ''
        END
    END AS content
FROM (
  SELECT 
    id,
    content,
    LOCATE('.', content, 500) AS idx,
    LENGTH(content) AS len
  FROM data
) AS data

您可以在此处查看它的运行情况: http://sqlfiddle.com/#!2/ac4d3/2 (出于明显的原因，我使用长度为10的字符串；))

You can see it in action here : http://sqlfiddle.com/#!2/ac4d3/2 (I use strings with length 10 for obvious reasons ;) )

这篇关于MYSQL:选择文本的一部分，然后根据点的位置将其剪切的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！