mysql语句怎么替换_mysql替换语句部分替换

迷南。 2022-10-25 05:50 118阅读 0赞

将cdb\_pms表subject字段中的Welcom to替换成 欢迎光临

UPDATE \`cdb\_pms\`

SET \`subject\` = REPLACE(\`subject\`, 'Welcome to', '欢迎光临')

WHERE INSTR(\`subject\`,'Welcome to') > 0

替换cdb\_posts表的message字段，将“viewthread.php?tid=3989”替换成“viewthread.php?tid=16546”

UPDATE \`cdb\_posts\`

SET \`message\`= REPLACE(\`message\`, 'viewthread.php?tid=3989', 'viewthread.php?tid=16546')

WHERE INSTR(\`message\`,'viewthread.php?tid=3989') > 0 ;

删除所有的空格

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = TRIM(\`pro\_pub\_time\`)

删除所有饱含'\['或者'\]'或者'.'的字符UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = REPLACE(\`pro\_pub\_time\`, '\[','')   WHERE INSTR(\`pro\_pub\_time\`,'\[') > 0

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = REPLACE(\`pro\_pub\_time\`, '\]','')   WHERE INSTR(\`pro\_pub\_time\`,'\]') > 0

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = REPLACE(\`pro\_pub\_time\`, '.','-')   WHERE INSTR(\`pro\_pub\_time\`,'.') > 0

替换所有含中文'-'的为英文'-'

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = REPLACE(\`pro\_pub\_time\`, '－','-')   WHERE INSTR(\`pro\_pub\_time\`,'－') > 0

将所有的年月都替换成'-'

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = REPLACE(\`pro\_pub\_time\`, '年','-')   WHERE INSTR(\`pro\_pub\_time\`,'年') > 0

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = REPLACE(\`pro\_pub\_time\`, '月','-')   WHERE INSTR(\`pro\_pub\_time\`,'月') > 0

将所有'2005-04-'这种类型的替换成'2005-04-01'

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = CONCAT( \`pro\_pub\_time\`, '01')   WHERE SUBSTRING\_INDEX( \`pro\_pub\_time\`, '-', -1) = '' AND LENGTH(\`pro\_pub\_time\`) > 0 AND LENGTH(\`pro\_pub\_time\`) > 5

将所有'2005-'这种类型替换成'2005-01-01'

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = CONCAT( \`pro\_pub\_time\`, '01-01') WHERE INSTR(\`pro\_pub\_time\`,'-') > 0 AND   LENGTH(\`pro\_pub\_time\`) = 5

将所有 饱含'-',但是位数小于8的改成追加'-01'

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = CONCAT( \`pro\_pub\_time\`, '-01') WHERE INSTR(\`pro\_pub\_time\`,'-') > 0 AND   LENGTH(\`pro\_pub\_time\`) < 8

将所有'2005'这样的改成'2005-01-01'

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = CONCAT(\`pro\_pub\_time\`,'-01-01') WHERE INSTR(\`pro\_pub\_time\`,'-') = 0 AND   LENGTH(\`pro\_pub\_time\`) = 4

最后将所有'2005-01-01'格式化成'2005年01月'

UPDATE \`es\_product\`   SET \`pro\_pub\_time\` = DATE\_FORMAT(\`pro\_pub\_time\`,'%Y年%m月') WHERE INSTR(\`pro\_pub\_time\`,'-') > 0