I am trying to grab variable length string from a primary string. Example:
ABC*12*1*name name****XX*123456789~
ABC*12*1*diffname diffname****XX*234567890~
ABC*12*1*diffname2 diffname2***XX*345678901~
I need to pull out the 'name name', 'diffname diffname', 'diffname2 diffname2'
etc from the string. And then replace the ' ' between the names with an asterisk - but, I cant just insert in the first space in the string, there could be multiple names, and so I would want to insert the '*' into the second, or third space, depending on the length of the name string.
SELECT
CHARINDEX('*1*',data)+3 AS startpos,
CHARINDEX('***',data) AS Endpos,
data
from #t
where data like '%ABC*12*1*%'
This gives me a start point and end point for the variable length string. So I try:
SELECT SUBSTRING(data,CHARINDEX('*1*',data)+3,CHARINDEX('***',data) -CHARINDEX('*1*',data)+3)
FROM #t
WHERE data like '%ABC*12*1*name%'
But this gives me
name n name aa*****X
as a result set, basically starting at the start point and then running well past the end point.
What am I doing wrong?
这部分是问题：
SELECT .....-CHARINDEX('*1*',data)+3
FROM .....
WHERE .....
您要减去，Endpos因此应该写在方括号中，如下所示：
-(CHARINDEX('*1*',data)+3)
如果除去括号，最后一部分应为-3：
-CHARINDEX('*1*',data)-3
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句