Concatenation (Join) and segmentation (Split) of MSSQL strings

concatenation (join) and segmentation (split) of MSSQL strings

Often a master uses select number from Master. spt_values WHERE type = ' P ', this is a good way to do it, but there are only 2048 digits, and the statement is too long and not convenient.

In short, a digital assistance table (100,000 or 1 million per person needs), you deserve to have.

2. Calendar table
Useful index: ★★★☆☆

The book "SQL Programming Style" suggests that a database tutorial for an enterprise should create a calendar table:
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

This table can be easily generated from the number-aid table in article 1th. You might need this table if you often need to do date processing.

You can also include special dates for business concerns in this table, such as opening Zichou City Day (stock industry), special anniversaries and festivals, birthdays for important employees, and so on. These dates are often difficult to calculate, such as the Chinese legal holidays (lunar question).

3. String concatenation (join) and segmentation (split)
Useful index: ★★★★★

This question is very common! In development, it is often necessary to divide a set of values in a comma-delimited string, or in turn to cut a comma-delimited string into a set of values.
This functionality can be easily implemented with SS2005 support for XML.

Single-Variable splicing and segmentation:
SQL code
--concatenation of a set of query results into a variable by the specified delimiter

DECLARE @datebases varchar (max)
Set @datebases = Stuff (
Select ', ' +name
From sys.databases
Order BY name
FOR XML Path ('), 1, 1, ')
Select @datebases
--Splits an incoming parameter into a table by the specified delimiter
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)

Batch splicing and segmentation:
SQL code
--Test data:
CREATE TABLE #tojoin (
TableName varchar is not NULL,
ColumnName varchar is not NULL,
Primary key Clustered (Tablename,columnname))
Go
CREATE TABLE #tosplit (
TableName varchar 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

--concatenation (join), SQL Server 2005 for XML extensions can turn a list into a string:
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

--Segmentation (split), using SQL Server 2005 for XQuery support:
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)

It should be noted that if the separator is ";" Or the string value contains XML special characters (such as &, <, >, and so on), the above methods may not be processed.