当前位置：首页 > news >正文

mysql截取最后一个字符之前的数据

news 2025/7/21 2:12:17

1、mysql截取最后一个字符之前的数据

select 
--截取斜杠之前的数据REVERSE(SUBSTR(REVERSE('SPNH-dfg-2012') ;
--截取斜杠后的数据    INSTR(REVERSE('SPNH-fg-2012'),'-')+1))

2、mysql获取最后一个字符后的数据

select	 SUBSTRING_INDEX('SPNH-dfg-2012','-',-1)

3、mysql更新某个字段IP

update anchoremc_file_log set 
address=replace(address,'192.168.1.29','111.175.140.46');update anchoremc_dailypaper_examine_case set 
picture_url=replace(picture_url,'192.168.1.29','111.175.140.46')

实例插入语句:

INSERT INTO anchoremc_dailypaper_examine_case_new ( case_code, enterprise_name, enterprise_code, professional, select_device, device_name_wh, inspection_desc, handling_suggestions, problem_pro, inspection_date, picture_url, data_sources )
SELECTcase_code,enterprise_name,enterprise_code,professional,select_device,device_name_wh,inspection_desc,handling_suggestions,problem_pro,CONCAT(year,'-',moth,'-',day) inspection_date,'[]' picture_url,(CASE data_sourcesWHEN '' THEN 3WHEN NULL THEN 3WHEN '人工录入' THEN 1else 0END ) data_sourcesFROManchoremc_dailypaper_examine_case_excelnew;SELECT * FROM anchoremc_dailypaper_examine_case_newwhere inspection_date = '2008-01-01';

5、更新案例编码

update anchoremc_dailypaper_examine_case
set case_code_pre=REVERSE(SUBSTR(REVERSE(case_code) , INSTR(REVERSE(case_code),'-')+1)),case_code_end=SUBSTRING_INDEX(case_code,'-',-1) ,year_time= YEAR(inspection_date),moth_time=MONTH(inspection_date)WHEREyear_time IS NULL

6、更新链接

update anchoremc_file_log set address=replace(address,'192.168.1.29','111.175.140.46')
update anchoremc_dailypaper_examine_case set picture_url=replace(picture_url,'192.168.1.29','111.175.140.46')