Everyone a great god, and the SQL for a field characters in,1,2,7,8 (3), the character type varchar, how to write a SQL statement can make character sorting display (1,2,3,7,8)?

CodePudding user response:

 
The create table # t (x varchar (100)) 

Insert into # t (x) select '3,1,2,7,8' 

The create function dbo. Resort mixes (@ x varchar (100)) 
Returns a varchar (100) 
As 
The begin 
Declare @ y a varchar (100); 

As with t (
The select val=o.v alue (', 'int') 
The from (select S=the convert (XML, '& lt; R>  '+ replace (@ x,', ', '& lt;/n>  '+' & lt;/n>  T 
 '))Cross the apply S.n odes ('/r/n) x (o)) 
Select @ y=isnull (@ y + ', ', ') + rtrim (val) 
The from t 
The order by val 

Return the @ y 
End 


The update t 
The set t.x=dbo. Resort mixes (x) 
The from # t t 

Select * from # t 

/* 
X 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1,2,3,7,8 

Line (1) affected 
*/

CodePudding user response:

The

reference 1/f, started the first reply:

 
The create table # t (x varchar (100)) 

Insert into # t (x) select '3,1,2,7,8' 

The create function dbo. Resort mixes (@ x varchar (100)) 
Returns a varchar (100) 
As 
The begin 
Declare @ y a varchar (100); 

As with t (
The select val=o.v alue (', 'int') 
The from (select S=the convert (XML, '& lt; R>  '+ replace (@ x,', ', '& lt;/n>  '+' & lt;/n>  T 
 '))Cross the apply S.n odes ('/r/n) x (o)) 
Select @ y=isnull (@ y + ', ', ') + rtrim (val) 
The from t 
The order by val 

Return the @ y 
End 


The update t 
The set t.x=dbo. Resort mixes (x) 
The from # t t 

Select * from # t 

/* 
X 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1,2,3,7,8 

Line (1) affected 
*/

Accordance with your writing, a great god, and I found that the presence of duplicate data sorted ah, this is why

CodePudding user response:

 
Alter the function dbo. Resort mixes (@ x varchar (8000)) 
Returns a varchar (8000) 
As 
The begin 
Declare @ y a varchar (8000); 

As with t (
The select val=o.v alue (', 'int') 
The from (select S=the convert (XML, '& lt; R>  '+ replace (@ x,', ', '& lt;/n>  '+' & lt;/n>  T 
 '))Cross the apply S.n odes ('/r/n) x (o)) 
Select @ y=isnull (@ y + ', ', ') + rtrim (val) 
The from t 
The order by val 

Return the @ y 
End