时间戳

首页 > 解决方案 > 删除列中除字母和单个空格之外的所有内容

php - 删除列中除字母和单个空格之外的所有内容

问题描述

列title是这样的： 

14 blue5 sky
3 5gold sun"
'/lorem   ip25sum
  light moon

我需要删除除字母和单词之间的单个空格之外的所有内容。

所以上面的例子应该是： 

blue sky
gold sun
lorem ipsum
light moon

有什么帮助吗？

phpMyAdmin： 

Server: 127.0.0.1 via TCP/IP
Server type: MariaDB
Server connection: SSL is not being used Documentation
Server version: 10.1.37-MariaDB - mariadb.org binary distribution
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)

标签： phpmysql

解决方案

You should be able to use a nested REGEXP_REPLACE to achieve what you want:

SELECT REGEXP_REPLACE(
         REGEXP_REPLACE(
           REGEXP_REPLACE(title, '[^[:alpha:] ]', ''),
           '^\\s+', ''),
         '\\s+', ' ') AS newtitle FROM table1

The first REGEXP_REPLACE removes any non-alphabetic characters; the second removes any spaces at the beginning of the string, and the final one replaces any sequences of one or more spaces with a single space.

Output (for your sample data):

newtitle
blue sky
gold sun
lorem ipsum
light moon
čć đš hello

To update the values in the table, you can use this query:

UPDATE table1
SET title =  REGEXP_REPLACE(
               REGEXP_REPLACE(
                 REGEXP_REPLACE(title, '[^[:alpha:] ]', ''),
                 '^ ', ''),
                '\\s+', ' ')

Demo on dbfiddle

推荐阅读