sqlserver语句

#更加唯一id合并字段

SELECT
    唯一id,
    type = (
               STUFF(
                    (SELECT ',' + type
                     FROM table
                     WHERE 唯一id= A.唯一id
                     FOR xml path('')
                    ),1,1,''
                    )
                 ) into temp
FROM table A
GROUP by 唯一id

#拆分语句 根据articleUT 唯一号，拆分institutions 字段

WITH 测试1 AS ( 
SELECT articleUT,institutions,CHARINDEX(';',institutions) STA,CHARINDEX(';',institutions)-1 LENS FROM lunwen where countries like '%CHINA MAINLAND;%'
UNION ALL 
SELECT articleUT,institutions,CHARINDEX(';',institutions,STA+1) STA,CHARINDEX(';',institutions,STA+1)-STA-1 LENS FROM 测试1 WHERE STA<>0) 
SELECT articleUT,SUBSTRING(institutions,STA-LENS,LENS) as institutions into lunwen_institution_chai FROM 测试1 
WHERE STA<>0 option(maxrecursion 0)
 

#全角字符转半角字符
CREATE FUNCTION f_Convert
(
@str NVARCHAR(4000) , --要转换的字符串 
@flag BIT --转换标志，0转换成半角，1转换成全角
)
RETURNS NVARCHAR(4000)
AS
BEGIN

DECLARE @pat NVARCHAR(8) ,
@step INT ,
@i INT ,
@spc INT;

IF @flag = 0
SELECT @pat = N'%[。-～]%' ,
@step = -65248 ,
@str = REPLACE(@str, N'　 ', N' ');

ELSE
SELECT @pat = N'%[!-~]%' ,
@step = 65248 ,
@str = REPLACE(@str, N' ', N'　 ');

SET @i = PATINDEX(@pat COLLATE Latin1_General_BIN, @str);

WHILE @i > 0
SELECT @str = REPLACE(@str, SUBSTRING(@str, @i, 1),
NCHAR(UNICODE(SUBSTRING(@str, @i, 1))
+ @step)) ,
@i = PATINDEX(@pat COLLATE Latin1_General_BIN, @str);
RETURN(@str);
END;

SELECT '２００６ ';

select dbo.f_Convert('２００６ ',0);