Summary of several methods of implementing Split function in SQL (must see article) _mysql

Source: Internet
Author: User

Example 1

The code is as follows

Create function F_split (@SourceSql varchar (8000), @StrSeprate varchar)
returns @temp table (a varchar (100))
--function of the split function
--date:2003-10-14
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

SELECT * from Dbo.f_split (' 1,2,3,4 ', ', ')

a 
-------------------- 
1
2
3
4

(the number of rows affected is 4 rows)

Example 2

The code is as follows

--sql Server Split Function--author:zc_0101--Description:--support delimiter multibyte--use method--select * from DBO. F_sqlserver_split (' 1203401230105045 ', ' 0 ')--select * from DBO. F_sqlserver_split (' abc1234a12348991234 ', ' 1234 ')--select * from DBO. 
F_sqlserver_split (' ABC ', ', ') CREATE FUNCTION f_sqlserver_split (@Long_str varchar (8000), @split_str varchar (100)) RETURNS @tmp TABLE (ID inT IDENTITY PRIMARY KEY, short_str varchar (8000)) as BEGIN DECLARE @long_str_Tmp varchar (80 , @short_str varchar (8000), @split_str_length int SET @split_str_length = LEN (@split_str) IF CHARINDEX (@split_str, @ LONG_STR) =1 set @long_str_Tmp =substring (@Long_str, @split_str_length +1,len (@Long_str)-@split_str_length) ELSE SET @ long_str_tmp= @Long_str IF CHARINDEX (REVERSE (@split_str), REVERSE (@long_str_Tmp)) >1 SET @long_str_Tmp = @long_str_ tmp+ @split_str ELSE SET @long_str_Tmp = @long_str_Tmp IF CHARINDEX (@split_str, @long_str_Tmp) =0 Insert into @tmp select @l Ong_str_tmp ELSE BEGIN while CHARINDEX (@spl (www.jb51.net) it_str, @long_str_TMP) >0 BEGIN SET @short_str =substring (@long_str_Tmp, 1,charindex (@split_str, @long_str_Tmp)-1) DECLARE @long_str_ Tmp_len int, @split_str_Position_END int set @long_str_Tmp_LEN = Len (@long_str_Tmp) Set @split_str_Position_END = Len (@sh ORT_STR) + @split_str_length SET @long_str_Tmp =reverse (SUBSTRING (REVERSE (@long_str_Tmp), 1, @long_str_Tmp_LEN-@split _str_position_end) IF @short_str <> ' Insert into @tmp select @short_str End

Example 3

sql2000andsql2005 Practical Split function

The code is as follows

sql2000 CREATE FUNCTION [dbo]. 
[Splitstring_array] 

(@string nvarchar (4000), @split char (1))  RETURNS @array table (onestr nvarchar) as BEGIN declare @v_code varchar (MB)--zell 2006-05-26--set @string = Replace (@string, ', @split)--set @string = replace (@string, ', ', @split) while Len (@string) > 0 BEGIN if Charinde X (@split, @string, 1)!= 0 begin set @v_code = substring (@string, 1,charindex (@split, @string, 1)-1) Set @string = substring ( @string, CHARINDEX (@split, @string, 1) +1,len (@string)) End else if charindex (@split, @string, 1) = 0 Begin Set @v_code = @str ing set @string = ' End insert to @array (ONESTR) VALUES (@v_code) end return sql2005 CREATE function [dbo]. 
[Func_splitid] 
(@str varchar (max), @split varchar (10)) RETURNS @t Table (C1 nvarchar) as BEGIN DECLARE @x XML SET @x = CONVERT (XML, ' <items><item id= ' + REPLACE ( @str, @split, '/><item id= ') + '/></items> ') INSERT into @t SELECT x.item.value (' @id [1] ', ' nvarchar(m) ') from @x.nodes ('//items/item ') as x (item) 
 

The above in this SQL to implement the split function of several methods summarized (must read) is a small series to share all the content, hope to give you a reference, but also hope that we support the cloud habitat community.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.