SQL Server separates strings, finds all child classes under the parent class, deletes duplicate strings, and calculates the number of times a string appears in another character

/* Function for separating strings under DNT */

Create Function [dnt_split]
(
@ Splitstring nvarchar (4000 ),
@ Separator char (1) = ','
)
Returns @ splitstringstable table
(
[Item] nvarchar (200)
)
As
Begin
Declare @ currentindex int
Declare @ nextindex int
Declare @ returntext nvarchar (200)
Select @ currentindex = 1
While (@ currentindex <= datalength (@ splitstring)/2)
Begin
Select @ nextindex = charindex (@ separator, @ splitstring, @ currentindex)
If (@ nextindex = 0 or @ nextindex is null)
Select @ nextindex = datalength (@ splitstring)/2 + 1

Select @ returntext = substring (@ splitstring, @ currentindex, @ nextindex-@ currentindex)
Insert into @ splitstringstable ([item])
Values (@ returntext)

Select @ currentindex = @ nextindex + 1
End
Return
End

 

/* Search for all subclasses under a category */

Create Function f_child (@ typeid)
Returns @ Re table (typeid int, level INT)
As
Begin
Declare @ l int
Set @ l = 0
Insert @ Re select @ typeid, @ l
While @ rowcount> 0

Begin
Set @ l = @ L + 1
Insert @ Re select a. typeid, @ l
From product_type a join @ Re B on A. fatherid = B. typeid
Where B. Level = L-1
End
Return
End

 

/* Delete duplicate strings separated by commas */

Create Function deleterepeatstrs
(@ STRs varchar (100 ))
Returns varchar (100)
As
Begin
Declare @ norepeatstr varchar (100)
Declare @ index int
Set @ norepeatstr =''
Declare @ ID varchar (10)
Declare repeatcursor cursor for select * From dnt_split (@ STRs ,',')
Open repeatcursor
Fetch next from repeatcursor into @ ID
While @ fetch_status = 0
Begin
Select @ Index = charindex (@ ID, @ norepeatstr, 0)
If @ Index = 0
Begin
Set @ norepeatstr = @ norepeatstr + @ ID + ','
End
Fetch next from repeatcursor into @ ID
End
Set @ norepeatstr = substring (@ norepeatstr, 1, Len (@ norepeatstr)-1)
Close repeatcursor
Deallocate repeatcursor
Return @ norepeatstr
End

 

/* Calculate the number of times parameter 2 appears in parameter 1 */

Alter function cisum (@ thestr varchar (1000), @ searchstr varchar (100 ))
Returns smallint
As
Begin
Declare @ A smallint, @ B smallint
Set @ A = 0
Set @ B = 1
While @ B <= Len (@ thestr)
Begin
If substring (@ thestr, @ B, Len (@ searchstr) = @ searchstr begin set @ A = @ A + 1 end
Set @ B = @ B + 1
End
Return @
End