Various string functions and Instances

-- Various string functions

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_splitstr] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_splitstr]
Go

-- 3.2.1 cyclic Truncation
Create Function f_splitstr (
@ S varchar (8000), -- string to be split
@ Split varchar (10) -- Data Separator
) Returns @ Re table (COL varchar (100 ))
As
Begin
Declare @ splitlen int
Set @ splitlen = Len (@ split + 'A')-2
While charindex (@ split, @ s)> 0
Begin
Insert @ Re values (left (@ s, charindex (@ split, @ s)-1 ))
Set @ s = stuff (@ s, 1, charindex (@ split, @ s) + @ splitlen ,'')
End
Insert @ Re values (@ s)
Return
End
Go

/* ===================================================== ========= */

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_splitstr] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_splitstr]
Go

-- 3.2.3.1 use the temporary splitting auxiliary Table Method
Create Function f_splitstr (
@ S varchar (8000), -- string to be split
@ Split varchar (10) -- Data Separator
) Returns @ Re table (COL varchar (100 ))
As
Begin
-- Create a secondary table for splitting (only table variables can be operated in user-defined functions)
Declare @ t table (ID int identity, B bit)
Insert @ T (B) Select top 8000 0 from syscolumns A, syscolumns B

Insert @ Re select substring (@ s, ID, charindex (@ split, @ s + @ split, ID)-ID)
From @ t
Where id <= Len (@ s + 'A ')
And charindex (@ split, @ split + @ s, ID) = ID
Return
End
Go

/* ===================================================== ========= */

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_splitstr] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_splitstr]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [tb_splitstr] ') and objectproperty (ID, n' isusertable') = 1)
Drop table [DBO]. [tb_splitstr]
Go

-- 3.2.3.2 use permanent partitioning of auxiliary tables
-- String splitting auxiliary table
Select top 8000 id = identity (INT,) into DBO. tb_splitstr
From syscolumns A, syscolumns B
Go

-- String splitting Handler
Create Function f_splitstr (
@ S varchar (8000), -- string to be split
@ Split varchar (10) -- Data Separator
) Returns table
As
Return (
Select Col = cast (substring (@ s, ID, charindex (@ split, @ s + @ split, ID)-ID) as varchar (100 ))
From tb_splitstr
Where id <= Len (@ s + 'A ')
And charindex (@ split, @ split + @ s, ID) = ID)
Go

/* ===================================================== ========= */

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_splitstr] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_splitstr]
Go

-- 3.2.5 split data items by numbers and non-numbers again
Create Function f_splitstr (
@ S varchar (8000), -- string to be split
@ Split varchar (10) -- Data Separator
) Returns @ Re table (no varchar (100), value varchar (20 ))
As
Begin
-- Create a secondary table for splitting (only table variables can be operated in user-defined functions)
Declare @ t table (ID int identity, B bit)
Insert @ T (B) Select top 8000 0 from syscolumns A, syscolumns B

Insert @ Re
Select No = reverse (stuff (COL, 1, patindex ('% [^-^. ^ 0-9] % ', Col + 'A')-1 ,'')),
Value = reverse (left (COL, patindex ('% [^-^. ^ 0-9] %', Col + 'A')-1 ))
From (
Select Col = reverse (substring (@ s, ID, charindex (@ split, @ s + @ split, ID)-ID ))
From @ t
Where id <= Len (@ s + 'A ')
And charindex (@ split, @ split + @ s, ID) = ID)
Return
End
Go

/* ===================================================== ========= */

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_splitstr] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_splitstr]
Go

-- 3.2.6 split text message data
Create Function f_splitstr (@ s varchar (8000 ))
Returns @ Re table (split varchar (10), value varchar (100 ))
As
Begin
Declare @ splits table (split varchar (10), splitlen as Len (split ))
Insert @ splits (split)
Select 'ac' Union all
Select 'bc' Union all
Select 'cc' Union all
Select 'dc'
Declare @ pos1 int, @ pos2 int, @ split varchar (10), @ splitlen int
Select top 1
@ Pos1 = 1, @ split = Split, @ splitlen = splitlen
From @ splits
Where @ s like split + '%'
While @ pos1> 0
Begin
Select top 1
@ Pos2 = charindex (split, @ s, @ splitlen + 1)
From @ splits
Where charindex (split, @ s, @ splitlen + 1)> 0
Order by charindex (split, @ s, @ splitlen + 1)
If @ rowcount = 0
Begin
Insert @ Re values (@ split, stuff (@ s, 1, @ splitlen ,''))
Return
End
Else
Begin
Insert @ Re values (@ split, substring (@ s, @ splitlen + 1, @ pos2-@ splitlen-1 ))
Select top 1
@ Pos1 = 1, @ split = Split, @ splitlen = splitlen, @ s = stuff (@ s, 1, @ pos2-1 ,'')
From @ splits
Where stuff (@ s, 1, @ pos2-1, '') Like split + '%'
End
End
Return
End
Go

 

 

-- Test 1
If object_id ('test') is not null drop Table Test
Go
Create Table Test (ID int, text1 nvarchar (20 ))
If object_id ('pclass ') is not null drop table Pclass
Go
Create Table Pclass (ID int identity, userid int, classid nvarchar (20), class_order INT)
Go
Insert Test
Select 1, 'a1, A4, A5, A2 'Union all
Select 2, 'a, B, C' Union all
Select 3, '11, 2, H'

-- String splitting Handler
If object_id ('f _ splitstr ') is not null drop function f_splitstr
Go
Create Function f_splitstr (
@ S varchar (8000), -- string to be split
@ Split varchar (10) -- Data Separator
) Returns @ Re table (COL varchar (100), sort int identity)
As
Begin
Declare @ splitlen int
Set @ splitlen = Len (@ split + 'A')-2
While charindex (@ split, @ s)> 0
Begin
Insert @ Re values (left (@ s, charindex (@ split, @ s)-1 ))
Set @ s = stuff (@ s, 1, charindex (@ split, @ s) + @ splitlen ,'')
End
Insert @ Re values (@ s)
Return
End
Go

Declare @ ID int, @ text1 nvarchar (20)
Declare cur cursor for select * from test
Open cur
Fetch next from cur into @ ID, @ text1
While @ fetch_status = 0
Begin
Insert Pclass select @ ID, Col, sort from DBO. f_splitstr (@ text1 ,',')
Fetch next from cur into @ ID, @ text1
End
Close cur
Deallocate cur

Select * From Pclass

-- Result
/*
Id userid classid class_order
-----------------------------------------------------
1 1 A1 1
2 1 A4 2
3 1 A5 3
4 1 A2 4
5 2 A 1
6 2 B 2
7 2 C 3
8 3 11 1
9 3 2 2
10 3 H 3

(The number of affected rows is 10)

*/

 

 

-- Segment truncation Function)
Create Function DBO. f_getstr (
@ S varchar (8000), a string containing multiple data items
@ POS int, -- position of the data item to be obtained
@ Split varchar (10) -- Data Separator
) Returns varchar (100)
As
Begin
If @ s is null return (null)
Declare @ splitlen int
Select @ splitlen = Len (@ split + 'A')-2
While @ POS> 1 and charindex (@ split, @ s + @ split)> 0
Select @ Pos = @ pos-1,
@ S = stuff (@ s, 1, charindex (@ split, @ s + @ split) + @ splitlen ,'')
Return (isnull (left (@ s, charindex (@ split, @ s + @ split)-1 ),''))
End
Go