数据库语句实现后特定字段间截取
目标:获取某字段最新追加的字符串
例如
gender字段 获取每一条记录下最新追加的name值;
这里student表 gender字段数据格式为:name:12121 entry_id:1212212;第二条记录需要的是第二个name的值12.
实现结果:
MySQL版:
SELECT REVERSE(SUBSTR(REVERSE(gender) FROM instr(REVERSE(gender),BINARY REVERSE(‘entry_id:’))+LENGTH(‘entry_id:’) FOR instr(REVERSE(gender),BINARY REVERSE(‘name:’))- (instr(REVERSE(gender),BINARY REVERSE(‘entry_id’)) + LENGTH(‘entry_id:’)-1))) as NAME from student;
SQLSERVER版:
SELECT REVERSE(SUBSTR(REVERSE(gender)
FROM instr(REVERSE(gender),BINARY REVERSE(’; Entity Type:’))+LENGTH(’; Entity Type:’)
FOR instr(REVERSE(gender),BINARY REVERSE(‘Entry ID:’))- (instr(REVERSE(gender),BINARY REVERSE(’; Entity Type:’)) +
LENGTH(’; Entity Type:’)))) as NAME from student;
——实现思路就是substr函数,但只能拿第一个特定字段,所以必须用到reverse函数,将截取的字符串倒过来;