mssql 字符串的拼接(Join)与切分(Split)

mssql 字符串的拼接(join)与切分(split)

经常有高手使用select number from master..spt_values where type = 'p'，这是很妙的方法；但这样只有2048个数字，而且语句太长，不够方便。

总之，一个数字辅助表（10万还是100万根据个人需要而定），你值得拥有。

2. 日历表
有用指数：★★★☆☆

《sql编程风格》一书建议一个企业的数据库教程应该创建一个日历表：
sql code
create table calendar(
    date datetime not null primary key clustered,
    weeknum int not null,
    weekday int not null,
    weekday_desc nchar(3) not null,
    is_workday bit not null,
    is_weekend bit not null
)
go
with cte1 as(
    select
        date = dateadd(day,n,'19991231')
    from nums
    where n <= datediff(day,'19991231','20201231')),
cte2 as(
    select
        date,
        weeknum = datepart(week,date),
        weekday = (datepart(weekday,date) + @@datefirst - 1) % 7,
        weekday_desc = datename(weekday,date)
    from cte1)
--insert into calendar
select
    date,
    weeknum,
    weekday,
    weekday_desc,
    is_workday = case when weekday in (0,6) then 0 else 1 end,
    is_weekend = case when weekday in (0,6) then 1 else 0 end
from cte2

这个表可以很容易根据第1条的数字辅助表生成出来。如果经常需要进行日期处理的话，或许会需要这个表。

还可以在这个表中包含一些企业关心的特殊日期，比如开盘日休市日（股票行业）、特殊纪念日和节日、重要员工的生日，等等。这些日期往往是很难计算的，比如中国的法定节假日（农历问题）。

3. 字符串的拼接(join)与切分(split)
有用指数：★★★★★

这个问题非常常见！开发中经常需要把一组值以逗号分隔拼接在一个字符串，或是反过来把一个逗号分隔的字符串切分成一组值。
用ss2005对xml的支持可以非常方便地实现这个功能。

单变量的拼接与切分：
sql code
--将一组查询结果按指定分隔符拼接到一个变量中

declare @datebases varchar(max)
set @datebases = stuff((
        select ','+name
        from sys.databases
        order by name
        for xml path('')),1,1,'')
select @datebases
--将传入的一个参数按指定分隔符切分到一个表中
declare @sourceids varchar(max)
set @sourceids = 'a,bcd,123,+-*/=,x&y,<key>'
select v = x.n.value('.','varchar(10)')
from (
    select valuesxml = cast('<root>' +
        replace((select v = @sourceids for xml path('')),',','</v><v>') +
        '</root>' as xml)
) t
cross apply t.valuesxml.nodes('/root/v') x(n)

批量的拼接与切分：
sql code
--测试数据：
create table #tojoin(
    tablename varchar(20) not null,
    columnname varchar(20) not null,
    primary key clustered(tablename,columnname))
go
create table #tosplit(
    tablename varchar(20) not null primary key clustered,
    columnnames varchar(max) not null)
go
insert into #tojoin values('tblemployee','employeecode')
insert into #tojoin values('tblemployee','employeename')
insert into #tojoin values('tblemployee','hiredate')
insert into #tojoin values('tblemployee','jobcode')
insert into #tojoin values('tblemployee','reporttocode')
insert into #tojoin values('tbljob','jobcode')
insert into #tojoin values('tbljob','jobtitle')
insert into #tojoin values('tbljob','joblevel')
insert into #tojoin values('tbljob','departmentcode')
insert into #tojoin values('tbldepartment','departmentcode')
insert into #tojoin values('tbldepartment','departmentname')
go
insert into #tosplit values('tbldepartment','departmentcode,departmentname')
insert into #tosplit values('tblemployee','employeecode,employeename,hiredate,jobcode,reporttocode')
insert into #tosplit values('tbljob','departmentcode,jobcode,joblevel,jobtitle')
go

--拼接(join)，sql server 2005的for xml扩展可以将一个列表转成一个字串：
select
    t.tablename,
    columnnames = stuff(
        (select ',' + c.columnname
        from #tojoin c
        where c.tablename = t.tablename
        for xml path('')),
        1,1,'')
from #tojoin t
group by t.tablename

--切分(split)，使用sql server 2005对xquery的支持：
select
    t.tablename,
    columnname = c.columnname.value('.','varchar(20)')
from (
    select
        tablename,
        columnnamesxml = cast('<root>' + replace((select columnname = columnnames for xml path('')),',','</columnname><columnname>') + '</root>' as xml)
    from #tosplit
) t
cross apply t.columnnamesxml.nodes('/root/columnname') c(columnname)

需要注意的是，倘若分隔符为";"或者字符串值中包含xml特殊字符（比如&、<、>等等），以上方法可能会无法处理。

补充：数据库,Mssql