DECLARE @TB TABLE(COL VARCHAR(20)) INSERT @TB SELECT 'WH98012303' UNION ALL SELECT 'WH0102002' DECLARE @STR VARCHAR(100) SET @STR='' SELECT @STR=@STR+','''+COL+'''' FROM @TBSELECT STUFF(@STR,1,1,'') AS COL /* COL --------------------------------------------------------------------------------------------------- 'WH98012303','WH0102002' */
分解?/* 功能:实现split功能的函数 */create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(200)) as begin declare @i intset @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr)while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) endif @inputstr <> '\' insert @temp values(@inputstr)return end go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split
INSERT @TB
SELECT 'WH98012303' UNION ALL
SELECT 'WH0102002' DECLARE @STR VARCHAR(100)
SET @STR=''
SELECT @STR=@STR+','''+COL+'''' FROM @TBSELECT STUFF(@STR,1,1,'') AS COL
/*
COL
---------------------------------------------------------------------------------------------------
'WH98012303','WH0102002'
*/
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split
各分公司把 重点客户名单都发过来了(EXCEL文档)但是我每个都去打标点符号太累 ,能不能给这些编号 一起打上标点符号的
把WH01922,WH0793434......都打上标点 变成
'WH01922','WH0793434',.......
declare @a nvarchar(4000)
set @a='WH01922,WH0793434,WHO36789'
set @a=replace(@a,',',''',''')
select ''''+@a+''''
/*
'WH01922','WH0793434','WHO36789'
*/