SQL split string, SQL split

SQL split string, SQL split

Create function [dbo]. [fn_Split]
(
@ SourceSql nvarchar (max), -- source separator string
@ StrSeprate varchar (10) -- delimiter
)
Returns @ temp table (a nvarchar (max ))
As
Begin
Declare @ I int
Set @ SourceSql = rtrim (ltrim (@ SourceSql ))
Set @ I = charindex (@ StrSeprate, @ SourceSql)
While @ I> = 1
Begin
Insert @ temp values (left (@ SourceSql, @ i-1 ))
Set @ SourceSql = substring (@ SourceSql, @ I + 1, len (@ SourceSql)-@ I)
Set @ I = charindex (@ StrSeprate, @ SourceSql)
End
If @ SourceSql <> '\'
Insert @ temp values (@ SourceSql)
Return
End
Go

SQL split string

See what database you are.
SQL Server does not support regular-expression-like splitting. Only string functions are supported.
LEFT (name, CHARINDEX ('.', name, 0)
The middle part can be implemented using SUBSTRING instead of LEFT.

SQL string splitting ??

OK! The test is passed. Try it. Add more points if you feel better.

Exec decord_a '1a @ b2 @ c3 @ e4 @ f5'

--- This stored procedure can extract strings containing any @ character. The query analyzer or front-end dataset can be used to obtain results through temporary table output.

Create procedure decord_A
@ Str varchar (100)
AS
Declare @ n int, @ SN INT
SET @ N = LEN (@ STR)-LEN (REPLACE (@ STR, '@', '') -- extract the number of characters @
SET @ SN = 1
CREATE table # TEMP (sn int, cstr varchar (100 ))
WHILE @ SN <= @ N
BEGIN
Insert into # temp select @ SN, LEFT (@ STR, CHARINDEX ('@', @ STR)-1)
SELECT @ SN = @ SN + 1, @ STR = SUBSTRING (@ STR, CHARINDEX ('@', @ STR) + 1,100)
END
Insert into # temp select @ N + 1, @ STR
SELECT * FROM # TEMP order by sn
GO