今天哥们问了我一个这样的问题,用sql语句截取“abcd1234<guid>abe</guid>” 这个里面的abe经过测试终于搞定,现记录如下
select SUBSTRING('abcd1234<guid>abe</guid>',
(select (PATINDEX('%<guid>%','abcd1234<guid>abe</guid>')+6) as firstindex),
(select ((select PATINDEX('%</guid>%','abcd1234<guid>abe</guid>'))-(select (PATINDEX('%<guid>%','abcd1234<guid>abe</guid>')+6))) as lastindex))
说下思路,
1,提取<guid>的索引使用select (PATINDEX('%<guid>%','abcd1234<guid>abe</guid>'),这时获取的只是<的索引,所以要加6得到1的索引
2,提取中间字字符的长度,这里重点说下,我们可以用</guid>中的<索引减去<guid>中>索引 ,也就是
(select (
(select PATINDEX('%</guid>%','abcd1234<guid>abe</guid>'))-(select (PATINDEX('%<guid>%','abcd1234<guid>abe</guid>')+6)
3,用substring进行提取,(字符串,开始位置,提取长度)