Category:SQL
Article From:https://www.cnblogs.com/lanliying/p/9059877.html
SELECT m.content,o.order_price,o.id,m.id
FROM scp_home_msg m 
INNER JOIN scp_order o ON m.link_id=o.id

Change the amount of content inside the amount to order_price

1、Amount of money

SELECT substring_index(content,'Pay for it',-1)  FROM  scp_home_msg WHERE id=1096;
-- 1000.00"}

2、Get the last two characters

SELECT right(substring_index(content,'Pay for it',-1),2)  FROM  scp_home_msg WHERE id=1096;
-- "}

3、Obtain full amount

SELECT substring_index(substring_index(content,'Pay for it',-1),right(substring_index(content,'Pay for it',-1),2),1)  FROM  scp_home_msg WHERE id=1096;
-- 1000.00

4、Get the result

SELECT substring_index(substring_index(content,'Pay for it',-1),right(substring_index(content,'Pay for it',-1),2),1) jieGuo,
m.content content,o.order_price orderPrice,o.id orderId,m.id mId
FROM scp_home_msg m 
INNER JOIN scp_order o ON m.link_id=o.id;

5、Comparison of data obtained by inconsistencies in the amount of money

SELECT STRCMP(substring_index(substring_index(m.content,'Pay for it',-1),right(substring_index(m.content,'Pay for it',-1),2),1),o.order_price) jieGuo,
substring_index(substring_index(content,'Pay for it',-1),right(substring_index(content,'Pay for it',-1),2),1) jj,
o.order_price orderPrice,m.content content,o.id orderId,m.id mId
FROM scp_home_msg m 
INNER JOIN scp_order o ON m.link_id=o.id;

6、Get the results of different data

SELECT STRCMP(substring_index(substring_index(m.content,'Pay for it',-1),right(substring_index(m.content,'Pay for it',-1),2),1),o.order_price) jieGuo,
substring_index(substring_index(content,'Pay for it',-1),right(substring_index(content,'Pay for it',-1),2),1) jj,
o.order_price orderPrice,m.content content,o.id orderId,m.id mId
FROM scp_home_msg m 
INNER JOIN scp_order o ON m.link_id=o.id
WHERE STRCMP(substring_index(substring_index(m.content,'Pay for it',-1),right(substring_index(m.content,'Pay for it',-1),2),1),o.order_price) <> 0;

7、Replace the data

SELECT m.content AS Before the replacement,REPLACE(content,substring_index(substring_index(content,'Pay for it',-1),right(substring_index(content,'Pay for it',-1),2),1),o.order_price) as After the replacement,O.order_priceas The amount actually paid is substring_index (substring_index (content).'Pay for it',-1),right(substring_index(content,'Pay for it',-1),2),1) as Show the amount,O.id orderId, m.id mIdFROM scp_home_msg m 
INNER JOIN scp_order o ON m.link_id=o.id
WHERE substring_index(substring_index(m.content,'Pay for it',-1),right(substring_index(m.content,'Pay for it',-1),2),1) <> o.order_price;

 

Summary:

1、replace(object, search,replace)
Replace all search in object with replace, select replace (‘www.163.com’,’w’,’Ww’) —> WwW wWw.163.com
Example: replace the AA in the name field in table table with BB, update table set name=replace (name,’aa’,’bb’)

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

2、mysqlThe string interception is derived from: https://www.cnblogs.com/shuaiandjun/p/7197450.html? Utm_source=itdadao& utm_medium=referrAl

1left(str,index) Start intercepting from index on the left2right(str,index)Start intercepting from right index3substring(str,index)When index>0Start from the left to the end as index<0Start intercepting from the right to the end as index=0Return to the empty4substring(str,index,len) Intercepting STR, starting from index, intercepting the length of the len5、substring_index(str,delim,count),strIs the string to intercept, delim is the interception of the field count is from where to start intercepting (for 0 is the left zeroth start, the 1 left to start the first selection of the left,-1The right one starts from the first one on the right6、subdate(date,day)Intercept time, time minus day7、subtime(expr1,expr2)  Time minute expr1-expr2

 

Link of this Article: MySQL intercepts a string of strings

Leave a Reply

Your email address will not be published. Required fields are marked *