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
    &n bsp;   date = dateadd(day,n,'19991231')
    from nums
    where n <= d atediff(day,'19991231','20201231')),
cte2 as(
    select
         date,
        weeknum = datepart(week,date),
         weekday = (datepart(weekday,date) + @@datefirst - 1) % 7,
    & nbsp;   weekday_desc = datename(weekday,date)
    from cte1)
--insert into calendar
select
    date,
  & nbsp; 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< br> 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,
&nbs p;   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
  & nbsp;     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& gt;<columnname>') + '</root>' as xml)
from #tosplit
) t
cross apply t.columnnamesxml.nodes('/root/columnname') c(columnname)

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