MySQL Intercept replaces a string

SELECT m.content,o.order_price,o.id,m.id  from  INNERJOIN on m.link_id=o.id

Change the amount of content into Order_price

1. Get the amount

SELECT substring_index (content,' paid ￥',-1) from     WHERE id=1096; -- 1000.00 "}

2. Get the last two characters

SELECT  Right (Substring_index (content,' paid ￥',-1),2)   from  WHERE id=1096; --  "}

3. Get the full amount

SELECTSubstring_index (Substring_index (content,'paid ￥',-1), Right(Substring_index (Content,'paid ￥',-1),2),1) fromScp_home_msgWHEREId=1096;--1000.00

4. Get Results

SELECTSubstring_index (Substring_index (content,'paid ￥',-1), Right(Substring_index (Content,'paid ￥',-1),2),1) jieguo,m.content content,o.order_price orderprice,o.id orderid,m.id mId fromscp_home_msg MINNER JOINScp_order o onm.link_id=O.id;

5, to obtain the amount of inconsistent data for comparison

SELECTSTRCMP (Substring_index (Substring_index (M.content,'paid ￥',-1), Right(Substring_index (M.content,'paid ￥',-1),2),1), O.order_price) Jieguo,substring_index (substring_index (content,'paid ￥',-1), Right(Substring_index (Content,'paid ￥',-1),2),1) jj,o.order_price orderprice,m.content content,o.id orderid,m.id mId fromscp_home_msg MINNER JOINScp_order o onm.link_id=O.id;

6. Get different data from the results

SELECTSTRCMP (Substring_index (Substring_index (M.content,'paid ￥',-1), Right(Substring_index (M.content,'paid ￥',-1),2),1), O.order_price) Jieguo,substring_index (substring_index (content,'paid ￥',-1), Right(Substring_index (Content,'paid ￥',-1),2),1) jj,o.order_price orderprice,m.content content,o.id orderid,m.id mId fromscp_home_msg MINNER JOINScp_order o onm.link_id=o.idWHERESTRCMP (Substring_index (Substring_index (M.content,'paid ￥',-1), Right(Substring_index (M.content,'paid ￥',-1),2),1), O.order_price)<> 0;

7. Replace data

SELECTM.content asBefore replacing,REPLACE(Content,substring_index (Substring_index (content,'paid ￥',-1), Right(Substring_index (Content,'paid ￥',-1),2),1), O.order_price) asafter replacement, O.order_price asAmount paid, Substring_index (substring_index (content,'paid ￥',-1), Right(Substring_index (Content,'paid ￥',-1),2),1) asShow amount, o.id orderid,m.id mId fromscp_home_msg MINNER JOINScp_order o onm.link_id=o.idWHERESubstring_index (Substring_index (M.content,'paid ￥',-1), Right(Substring_index (M.content,'paid ￥',-1),2),1)<>O.order_price;

Summarize:

1. Replace (object, Search,replace)
Replace all occurrences of search in object with Replace,select replace (' www.163.com ', ' w ', ' Ww ')--->www wWw.163.com
Example: Replacing AA in the Name field in table tables with Bb,update table set Name=replace (name, ' AA ', ' BB ')

From: http://www.jb51.net/article/25769.htm

2. mysql string interception from: https://www.cnblogs.com/shuaiandjun/p/7197450.html?utm_source=itdadao&utm_medium=referral

1、 Left(Str,Index) from the left index, start intercept2、 Right(Str,Index) from the right, index start intercept3、substring(Str,Index) when index>0 intercept from left to end when index<0 intercept from right until end when index=0 return Null4、substring(Str,Index,LenIntercept str, starting with index, to intercept len length5, Substring_index (Str, Delim,Count), Str is the string to intercept, Delim is the Intercept field where count is to start interception (for 0 is the No. 0 start on the left, and 1 bits to the left to start the first selection of the left one,-1 from the right, the first one, select the right6, Subdate (date, DayTime minus the day after.7, Subtime (expr1,expr2) seconds expr1-Expr2

MySQL Intercept replaces a string