Let's first give the question and the result, the topic is just seen from the internet, many friends will ask this answer is not there? Wrong, if carefully look at the difference is big, not many said directly into the topic it:
That's what we're going to do, and here are three ways to do it:
method One:FUNCTION [dbo]. [Strsplit] (
@String nvarchar (MAX),--the string to be split
@ delimiter nvarchar (ten) = ', ',--separator
@0string int=0--Filter empty string for "1"
) Returns @array table (item nvarchar (MAX), ID int)
As
Begin
declare @begin int, @end int,@l int, @item nvarchar (MAX), @ID int=0;
SET @l=len (@ delimiter)
Set @begin = 1;
Set @end =charindex (@ delimiter, @String, @begin);
while (@end <>0)
Begin
Set @item = substring (@String, @begin, @[email protected]);
IF @0string<>1 or Len (@item) >0 begin
SET @[email protected]+1;
Insert into @array (Item,id) VALUES (@item, @ID);
End
Set @begin = @[email protected];
Set @end =charindex (@ delimiter, @String, @begin);
End
Set @item = substring (@String, @begin, Len (@String) [email protected]);
if (Len (@item) >0) begin
SET @[email protected]+1;
Insert into @array (Item,id) VALUES (@item, @ID);
End
Return
End we can see clearly the method a code is too large, not applicable because I found method two, I have a combination of his code to get the following methods: Method Two: Select REVERSE (SUBSTRING (REVERSE (name), CHARINDEX (" , REVERSE (name)), 6), SUBSTRING (name, CHARINDEX (", name), ten) from D everybody looked at the method two think already good, completely can, but for the people who like SQL, this is not what we think good, The code is too long, so I think will give a third scenario, this solution own data combination: Method Three: Select Left (Name, (CHARINDEX (", name)-1)), SUBSTRING (name, CHARINDEX (", name) , the plan from D above is I finally give out, if there is a better SQL, welcome Bo friends A lot of advice.
The Name column is split into two columns for the following methods
