mysql - 将最后几个词移到同一列的前面

Question

我正在尝试创建一个命令，该命令将从一列中取出最后几个单词并将其添加到前面(并删除逗号)，例如:

Update:
Engineering and Applied Science (SEAS), The Fu Foundation School of


To:
The Fu Foundation School of Engineering and Applied Science (SEAS)

目前我有这个

update ONLINE_GIFT_DIVISIONS
set test_newgiving_name = case
                    when DIVISION_TITLE LIKE '%, School of%' then
                     ''
                    when DIVISION_TITLE LIKE '%, Graduate School of%' then
                     ''
                    when DIVISION_TITLE LIKE '%, Mailman School of%' then
                     ''
                    when DIVISION_TITLE LIKE '%, The Fu Foundation School of%' then
                     ''
                  end
where division_title like '%, School of%'
where division_title like '%, Graduate School of%'
where division_title like '%, Mailman School of%'
where division_title like '%, The Fu Foundation School of%';

Answer 1

这是 Oracle PL/SQL 吗？在这种情况下，您可以使用正则表达式来交换这两个部分(但不是最好的性能):

使用 Oracle REGEXP_REPLACE:

你有 2 个元素包含在 () 中，可以是任何东西 -> .*，用逗号分隔:这给你一个这样的模式: (.*),(.*)。然后第一个元素由 \1 寻址，第二个元素在替换中由 \2 寻址 -> 替换字符串变为 \2\1。

SELECT 
  regexp_replace(str_var, '(.*),(.*)', '\2 \1'), str_var  FROM 
      (
           select 'Engineering and Applied Science (SEAS), The Fu Foundation School of' str_var from dual
union all  select 'ISIMA the best, Mailman School of'                                           from dual
union all  select 'another text, splitted with comma, but if there is another ? '               from dual
  ) ;

用它来更新你的表:

update ONLINE_GIFT_DIVISIONS
  set test_newgiving_name = regexp_replace(DIVISION_TITLE, '(.*),(.*)' , '\2 \1')  
where division_title like '%, School of%'
   or division_title like '%, Graduate School of%'
   or division_title like '%, Mailman School of%'
   or division_title like '%, The Fu Foundation School of%';

希望对您有所帮助。