函数:
1、从左开始截取字符串
left(str, length)
说明:left(被截取字段,截取长度)
例:SELECT left(make_date,4) as year from t_sale_billing where make_date=’2017-06-24’;
2、从右开始截取字符串
right(str, length)
说明:right(被截取字段,截取长度)
例:SELECT right(make_date,4) as year from t_sale_billing where make_date=’2017-06-24’;
3、截取字符串
substring(str, pos)
substring(str, pos, length)
说明:substring(被截取字段,从第几位开始截取)
substring(被截取字段,从第几位开始截取,截取长度)
例:SELECT substring(make_date,4) as year from t_sale_billing where make_date=’2017-06-24’;
SELECT substring(make_date,4,6) as year from t_sale_billing where make_date=’2017-06-24’;
SELECT substring(make_date,-4,6) as year from t_sale_billing where make_date=’2017-06-24’;
(注:如果位数是负数 如-5 则是从后倒数位数,到字符串结束或截取的长度)
4、按关键字截取字符串
substring_index(str,delim,count)
说明:substring_index(被截取字段,关键字,关键字出现的次数)
例:SELECT SUBSTRING_INDEX(make_date,’-‘,1) as year from t_sale_billing where make_date=’2017-06-24’;
结果:2017
(注:如果关键字出现的次数是负数 如-2 则是从后倒数,到字符串结束)
例子:
BEGIN
DECLARE year VARCHAR(20) DEFAULT 0;
DECLARE month VARCHAR(20) DEFAULT 0;
DECLARE day VARCHAR(20) DEFAULT 0;
SELECT SUBSTRING_INDEX(make_date,'-',1) into year from t_sale_billing where make_date='2017-06-24';
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(make_date,'-',2),'-',-1)into month
from t_sale_billing where make_date='2017-06-24';
SELECT
SUBSTRING_INDEX(make_date,'-',-1) into day from t_sale_billing where make_date='2017-06-24';
INSERT into t_sale_buy_memo (sale_mingxi_id,sale_bill_code,cus_code,buy_bill_code,sup_code,group_code) VALUES (year,month,day,0,0,'JL_5');
END