我在查询中得出如下值:
A0080RD035C3S,35220000000,30120000000,30120100000,3012-100030,4012-100030我想取最后两个逗号之间的值,取出来结果为: 3012-100030查询出来的值长度不定,里面会有3个逗号的,4个逗号的或更多,
但是我只要最后两个逗号之间的值,要如何处理?
A0080RD035C3S,35220000000,30120000000,30120100000,3012-100030,4012-100030我想取最后两个逗号之间的值,取出来结果为: 3012-100030查询出来的值长度不定,里面会有3个逗号的,4个逗号的或更多,
但是我只要最后两个逗号之间的值,要如何处理?
A0080RD035C3S,35220000000,30120000000,30120100000,3012-100010,4012-100010
A0080RD035C3S,35220000000,30120000000,30120100000,3012-100020,4012-100020
A0080RD035C3S,35220000000,30120000000,30120100000,3012-100021,4012-100021
A0080RD035C3S,35220000000,30120000000,30120100000,3012-100030,4012-100030
A0080RD035C3S,35220000000,30120000000,30120100000,3012-100031,4012-100031
A0080RD035C3S,35220000000,30120000000,30120100000,3012-100040,4012-100040
A0080RD035C3S,35220000000,30120000000,30120100000,3012-100041,4012-100040
A0080RD035C3S,35220000000,30120000000,30120400000,3012-400080,4012-400080值是通过游标显出,列名:MD200
select
reverse(
substring(
-- 将字符串列反转
reverse('A0080RD035C3S,35220000000,30120000000,30120100000,3012-100030,4012-100030'),
-- 截取第一个逗号出现的位置(原字符串中倒数第一个)
charindex(',',reverse('A0080RD035C3S,35220000000,30120000000,30120100000,3012-100030,4012-100030')) + 1,
-- 计算第一个和第二个逗号之间的长度(原字符串中倒数第一个和倒数第二个之间的距离)
charindex(
',',
substring(
reverse('A0080RD035C3S,35220000000,30120000000,30120100000,3012-100030,4012-100030'),
charindex(',',reverse('A0080RD035C3S,35220000000,30120000000,30120100000,3012-100030,4012-100030')) + 1,
datalength('A0080RD035C3S,35220000000,30120000000,30120100000,3012-100030,4012-100030') - charindex(',',reverse('A0080RD035C3S,35220000000,30120000000,30120100000,3012-100030,4012-100030'))
)
) - 1
)
-- 截取后需要再反转一次(之前字符串列做过反转)
)
from a把字符串部分换成列名,即是你要求的结果。这个有点繁琐~~~~
DECLARE @Str VARCHAR(100) = 'A0080RD035C3S,35220000000,30120000000,30120100000,3012-100010,4012-100010'SELECT REVERSE(LEFT(REPLACE(REVERSE(@str),LEFT(REVERSE(@str),CHARINDEX(',',REVERSE(@Str))),''),11))
其中,23位从右往左字符数量,11为从左往右字符数。