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 it.

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

The

SQL Programming style book suggests 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, etc. 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
--splicing a set of query results into a variable by a 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 concatenation and segmentation:
SQL code
--Test data:
CREATE TABLE #tojoin (
    tablename varchar) NOT NULL,
    columnname varchar 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 ')
inserts into #tojoin values (' Tblemployee ', ' hiredate ')
INSERT into #tojoin values (' Tblemployee ', ' Jobcode ')
inserts into #tojoin values (' Tblemployee ', ' Reporttocode ')
INSERT into #tojoin values (' tbljob ', ' Jobcode ')
inserts into #tojoin values (' Tbljob ', ' jobtitle ')
INSERT into #tojoin values (' tbljob ', ' joblevel ')
inserts into #tojoin values (' Tbljob ', ' Departmentcode ')
INSERT into #tojoin values (' tbldepartment ', ' Departmentcode ')
Insert into #tojoin values (' tbldepartment ', ' departmentname ')
Go
inserts 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
         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 ><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.