澳门鸿运国际,让你建站更便捷和轻松!
您当前的位置:鸿运娱乐 > 网络编程 > Mssql教程
mssql 字符串的拼接(Join)与切分(Split)
来源:澳门鸿运国际  时间:2010-07-07 10:39:27

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特殊字符(比如&、<、>等等),以上方法可能会无法处理。
 

责任编辑:admin

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
CopyRight 2008-2009, favnav.com, Inc. All Rights Reserved
皖ICP备09015033号