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

Source: Internet
Author: User
Tags mssql

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
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
With Cte1 as (
Date = DATEADD (Day,n, ' 19991231 ')
From Nums
where n <= datediff (day, ' 19991231 ', ' 20201231 ')),
Cte2 as (
Weeknum = DatePart (week,date),
Weekday = (DATEPART (weekday,date) + @ @datefirst-1)% 7,
Weekday_desc = Datename (weekday,date)
From Cte1)
--insert into calendar
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))
CREATE TABLE #tosplit (
TableName varchar NOT NULL primary key clustered,
ColumnNames varchar (max) NOT NULL)
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 ')
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 ')

--concatenation (join), SQL Server 2005 for XML extensions can turn a list into a string:
     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:
ColumnName = C.columnname.value ('. ', ' varchar (20) ')
From (
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.