sqlserver 分割字元串和調用

傳入某種規則拼接字元串獲得數組（表）

ALTER FUNCTION [dbo].[fnSys_SplitString]

(

---字元串分割

@Str VARCHAR(MAX), --傳入的字元串

@SeprateStr VARCHAR(10)--分隔符

)

RETURNS @temp TABLE(Code VARCHAR(100)) --傳回一個Table

AS

BEGIN

DECLARE @i INT

SET @Str=REPLACE(@str,'''','')

SET @Str =RTRIM(LTRIM(@Str ))

SET @i=CHARINDEX(@SeprateStr,@Str )

WHILE @i>=1

BEGIN

INSERT @temp VALUES(LEFT(@Str ,@i-1))

SET @Str =SUBSTRING(@Str ,@i+1,LEN(@Str )[email protected])

SET @i=CHARINDEX(@SeprateStr,@Str )

END

IF @Str <>''

INSERT @temp VALUES(@Str )

RETURN

END

使用此函數拓展方法調用

CREATE PROC WriteUnionIDByBatch

@UnionIDs VARCHAR(MAX),@OpenIDs VARCHAR(MAX),@CompanyID VARCHAR(50),@AccountID VARCHAR(50)

AS

BEGIN

DECLARE @unionID VARCHAR(50),@openid VARCHAR(50)

CREATE TABLE #OpenIDTable

(

openid VARCHAR(50),

unionid VARCHAR(50)

)

INSERT #OpenIDTable

SELECT oldopenid,newopenid FROM dbo.[fnSys_SplitString_ChangeOpenID](@OpenIDs,@UnionIDs,',')

DECLARE mycursor CURSOR FOR

SELECT openid,unionid FROM #OpenIDTable

OPEN mycursor

FETCH NEXT FROM mycursor INTO @openid,@unionID

WHILE @@FETCH_STATUS = 0

BEGIN

IF(ISNULL(@unionID,''))>''

BEGIN

UPDATE dbo.EWMS_Web_Member SET UnionID [email protected] WHERE CompanyID = @CompanyID AND AccountID = @AccountID

END

FETCH NEXT FROM mycursor INTO @openid,@unionID

END

CLOSE mycursor

DEALLOCATE mycursor

END

轉載于:https://www.cnblogs.com/wangdrama/p/9706623.html