如何取第N个字元前的字串？

[benwu1210]

请问要如何把
「AAAAxxBBBBBBBxxCCCCxxDDDDDDDDDDDxxEEEEEExxFFFFFFFFFF」
取出
「AAAAxxBBBBBBBxxCCCC」
（即第三个xx前的字串）

谢谢

[xuam]

charindex()

[Mr_Nice]

DECLARE @i NVARCHAR(2000)

SET @i ='AAAAxxBBBBBBBxxCCCCxxDDDDDDDDDDDxxEEEEEExxFFFFFFFFFF'

SELECT SUBSTRING(@i,0,CHARINDEX('xx',@i,CHARINDEX('xx',@i,CHARINDEX('xx',@i)+1)+1))
--AAAAxxBBBBBBBxxCCCC

[lenovore]

引用 1 楼 xuam 的回复:

charindex()

SUBSTRING起始位置应该是1而非0吧？ SELECT SUBSTRING(@i,1,CHARINDEX('xx',@i,CHARINDEX('xx',@i,CHARINDEX('xx',@i)+1)+1))

[水族杰纶]

引用 2 楼 OrchidCat 的回复:

SQL code??123456DECLARE @i NVARCHAR(2000) SET @i ='AAAAxxBBBBBBBxxCCCCxxDDDDDDDDDDDxxEEEEEExxFFFFFFFFFF' SELECT SUBSTRING(@i,0,CHARINDEX('xx',@i,CHARINDEX('xx',@i,CHARINDEX('xx',@i)+1)+1)……

.

[jiangshun]

DECLARE @split VARCHAR(20),@i INT,@result VARCHAR(4000)
SET @split='xx'
SET @i=3

DECLARE @xml XML
DECLARE @str VARCHAR(4000)
SET @str='AAAAxxBBBBBBBxxCCCCxxDDDDDDDDDDDxxEEEEEExxFFFFFFFFFF'

set @xml='<value>'+REPLACE(@str,@split,'</value><value>')+'</value>'

SET @result=stuff(CAST( (SELECT @split+result FROM(
SELECT 
     T.C.value('.','VARCHAR(200)') AS result,flag=ROW_NUMBER() OVER (ORDER BY @xml.query('.').value('.','VARCHAR(4000)'))
from @xml.nodes('/value') T(C))g
WHERE flag<@i+1
FOR XML PATH('') )AS VARCHAR(max)),1,LEN(@split),'')

SELECT @result