Merge values into single column and one row using FOR XML PATH

  ;WITH CTE_MERGE_VALUES

  AS 

  (

  SELECT [TYPE], 

[NAME]

  FROM MASTER..SPT_VALUES WHERE TYPE <> 'P'

  )

    SELECT [TYPE], 

[NAME],

[NAME2] = (SELECT STUFF((SELECT DISTINCT ' | ' +  CAST([NAME] AS NVARCHAR(MAX)) FROM CTE_MERGE_VALUES B WHERE [TYPE]=A.[TYPE] ORDER BY 1 ASC FOR XML PATH('')), 1, 3, ''))

FROM CTE_MERGE_VALUES A

Group the sequential records, like continuous days or weeks etc

Create a date group by subtracting value generated by row_number function over a group

Sequential_Col - Row_Number() Over(Partition By Col_1, Col_2 Order by Sequential_Col)

for example

Extracting date value from string or sentence.

declare @Tab Table ( Col nvarchar(1000))
Insert into @Tab values ('1CC 3/4/13')
Insert into @Tab values ('04.25.14 (CC) SELF')
Insert into @Tab values ('3/13/14 1CC')
Insert into @Tab values ('COPY 9-28-12 ')
Insert into @Tab values ('11/8/13 1CC SON REQUESTING')
Insert into @Tab values ('4/20/12 1CC')
Insert into @Tab values ('1 COPY 4-12-13 SELF')
Insert into @Tab values ('1CC 2/4/00, 11/17/11 WIFE REQUESTING')
Insert into @Tab values ('COPY 7-11-14')
Insert into @Tab values ('5.29.12 1CC HUSBAND REQUESTING')
Insert into @Tab values ('7/14/14 1CC')
Insert into @Tab values ('CC1-6/28/13 11/12/13 1CC')

SELECT * FROM (

Select *, Case When Col like '%CC%' Then 'CC'
  When Col LIKE '%COPY%' Then 'COPY'
  Else NULL
  END   [Type],
CAST(
REPLACE(
REPLACE(
CASE
     WHEN PATINDEX('%[0-9][0-9][./-][0-9][0-9][./-][0-9]%', Col) > 0
     THEN SUBSTRING(Col, PATINDEX('%[0-9][0-9][./-][0-9][0-9][./-][0-9]%', Col), 8)

     WHEN PATINDEX('%[0-9][0-9][./-][0-9][./-][0-9]%', Col) > 0
     THEN SUBSTRING(Col, PATINDEX('%[0-9][0-9][./-][0-9][./-][0-9]%', Col), 7)

     WHEN PATINDEX('%[0-9][./-][0-9][0-9][./-][0-9]%', Col) > 0
     THEN SUBSTRING(Col, PATINDEX('%[0-9][./-][0-9][0-9][./-][0-9]%', Col), 7)

     WHEN PATINDEX('%[0-9][./-][0-9][./-][0-9]%', Col) > 0
     THEN SUBSTRING(Col, PATINDEX('%[0-9][./-][0-9][./-][0-9]%', Col), 6)
     ELSE NULL
   END
   , '.','/')
   , '-','/') as DATE ) as [DATE]
from @Tab ) X

Create multiple records for delimited values in single column

declare @Tab Table ( Col nvarchar(100), type nvarchar(100))

Insert into @Tab values ('abc','a,b,c,d')
Insert into @Tab values ('xyz','x,y,z')
Insert into @Tab values ('pqr','p,')
Insert into @Tab values ('rst',',')
Insert into @Tab values ('lmn',' ')
Insert into @Tab values ('opq','o,p,q,r,t,y,u,i,o,p,d')



Select *--Col, ltrim(rtrim(split_value))
From (
    Select *,  Cast('~x~'+Replace(d.type,',','~/x~~x~')+'~/x~' As XML) As types
    From @Tab d
) x
Cross Apply (
    Select ttsd.x.value('.', 'varchar(255)') as split_value
    From x.types.nodes('x') as ttsd(x)

) c

Split column with delimiter into multiple columns

Declare @Delimiter nvarchar(10) = 'enter delimiter'
Declare @InputTable nvarchar(2000) = 'enter input table name'
Declare @OutputTable nvarchar(2000) = 'enter output table name'
Declare @ColumnToSplit nvarchar(2000) = 'enter column to split'


Declare @lsql nvarchar(max)
Declare @treeDepth int

If Object_id('dbo.treeDepth') is not null 
Drop table dbo.treeDepth

CREATE TABLE dbo.treeDepth (depth INT)

declare @ltext nvarchar(max)= 'Select max(1+(len('+@ColumnToSplit+')- len(Replace('+@ColumnToSplit+','''+@Delimiter+''','''')))/(isnull(nullif(len('''+@Delimiter+'''),0),1))) from '+@InputTable

insert dbo.treeDepth EXEC(@ltext)

Select @lsql = isnull(@lsql+',','') + 
'xmlname.value(''/Node[1]/Node['+cast(number+1 as nvarchar)+']'',''varchar(1000)'') AS Col_'+cast(number+1 as nvarchar)+''
from master..spt_values where type = 'P' and number < (Select * from dbo.treeDepth)

set @lsql = '
WITH ForXML
AS
(
    SELECT *,
    CONVERT(XML,''''  
    + REPLACE('+@ColumnToSplit+','''+@Delimiter+''', ''

'') + '''') AS xmlname      FROM '+@InputTable+'
)

Select *, '+@lsql+' Into dbo.'+@OutputTable+' From ForXML


Alter table '+@OutputTable+' 
Drop column xmlname
' 

EXEC(@lsql)

Delimited column values into rows

Select * from 

  (Select Row_number() Over (Order by (select null)) r_num,

  CONVERT( XML, '' + REPLACE( 'a,b,c,d,e,f,g,h,i,j,k', ',', '' ) + '' ) AS XML_ROW) A

  

  CROSS APPLY 

        ( 

                SELECT DATA.ROW.value('.', 'VARCHAR(1000)') AS IND_ROW FROM A.XML_ROW.nodes('ROW') AS DATA(ROW)

        ) B

SQL Server procedure - Backup table data

Script :

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[BackupTable]

@SchemaName nvarchar(255),

@TableName nvarchar(255)

AS

Set @SchemaName = replace(replace(@SchemaName,'[',''),']','')

Set @TableName = replace(replace(@TableName,'[',''),']','')

declare @Tstamp nvarchar(max) = Replace(Replace(Replace(convert(varchar(25), getdate(), 120),'-',''),' ','_'),':','')

declare @InputTable nvarchar(max)= '['+@SchemaName+'].['+@TableName+']'

declare @OutputTable nvarchar(max)= '[bckup].['+@TableName+'_'+@Tstamp+']'

declare @lsql nvarchar(max) = '

Select * into '+@OutputTable+' from '+@InputTable+''

EXEC(@lsql)