Ms SQL server string

1. Scheme 1 is as follows:

-=============================================
- Description: split function - the Debug: select * from dbo. Fun_Split (' ABC: BC: C: D: E ', ':')
- the modification time:
- the modifier:
-=============================================
The create FUNCTION [dbo] [F_StringSplit]
(
@ SourceSql varchar (8000),
@ StrSeprate varchar (10)
)
RETURNS
@ TEMP_Table TABLE ([text] varchar (2000)), collate Chinese_Taiwan_Stroke_CS_AS) - Chinese_Taiwan_Stroke_CI_AS case-insensitive
AS
The BEGIN
DECLARE @ I int
The SET @ SourceSql=rtrim (ltrim (@ SourceSql))
If RIGHT (@ SourceSql, LEN (@ StrSeprate)) & lt;> @ StrSeprate - additional code
Set @ SourceSql=@ SourceSql + @ StrSeprate - additional code
The SET @ I=charindex (@ StrSeprate, @ SourceSql)
WHILE @ i>=1
The BEGIN
If rtrim (ltrim (left (@ SourceSql, @ I - 1)))='- additional code start
The begin
SET @ SourceSql=the substring (@ SourceSql, @ I + 1, len (@ SourceSql) - @ I)
The SET @ I=charindex (@ StrSeprate, @ SourceSql)
The continue
End - additional code end
INSERT the @ TEMP_Table VALUES (left (@ SourceSql, @ I - 1))
SET @ SourceSql=the substring (@ SourceSql, @ I + 1, len (@ SourceSql) - @ I)
The SET @ I=charindex (@ StrSeprate, @ SourceSql)
END
RETURN
END
- for example -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

Select * from F_StringSplit (', 45,89,66,78,59, 41, 4 ', ', ')
2. The scheme using XML way

The create function F_StringSplit
(
@ source varchar (8000),
@ splitStr varchar (20)
)
Returns the @ TMP table (result XML)
As
The begin
The set @ source=LTRIM (RTRIM (isnull (@ source, ")))
The set @ splitStr=LTRIM (RTRIM (isnull (@ splitStr, ")))
Declare @ XML, XML @ delStr varchar (6)
The set @ delStr='
If @ source<> "' and @ splitStr<> '
The begin
Set @=the CONVERT XML (XML, '& lt; V> '+ REPLACE (@ source, @ splitStr,' & lt;/v> '+' & lt;/v> ')
The set @ XML. The modify (' delete/v [.=SQL: variable (" @ delStr ")] ')
Insert into @ TMP select T.V.Q uery ('. ') from @ XML. The nodes ('/v/text () ') t (v)
End
Return
End
- for example -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

Select * from F_StringSplit (', 45,89,66,78,59, 41, 4 ', ', ')

CodePudding user response:

Thanks for sharing, it is recommended that the blog,

CodePudding user response:

Thanks for sharing

PS: if you use a used 2017, have their own functions,

CodePudding user response:

Thanks for sharing, it is recommended that the blog,
In addition, you can refer to my blog:
https://blog.csdn.net/yenange/article/details/79788877