How to write sql for document approval process

caoyuezj

There is a table used to store the business process, which is the definition of the approval process, as explained below
f_bmbm indicates the department that needs to be approved, f_xh indicates the sequence, and f_must indicates that the process must be reviewed by this department
create table tbnote_check_order
(
f_xh int Not Null,-
f_bmbm varchar (15) Not Null,-department code
f_must int Not Null, --must (0 no 1 yes)
Constraint tbnote_check_order_key Primary Key (f_xh)
)

In the specific business process, all approval information is stored in another field f_spyj.The format is stored in this way

| linebegin | --line prefix, which indicates the approval of a department
| lineend |-line suffix, which indicates that the approval of a department is over
bmbegin-the department prefix, which indicates the approval department
| bmend |-Department suffix, which indicates the approval department
ygbmbegin | --Employee prefix, which represents the employees of the approved department
ygbmend | --Employee suffix, which means the employee of the approval department

For example, the information stored in f_spyj is as follows:
| linebegin | 20070101 | bmbegin | 001 | bmend || ygbmbegin | yg01 | ygbmend | Reviewed by

The question now is as follows:
1.How to judge that a certain department's approval is already in
2.How to judge that all approvals have been completed
3.How to delete an approval message

fanfan1980

--1
select * from tbName where charindex ('| bmbegin | department ID | bmend |', f_spyj)> 0

caoyuezj

4.How to not display these marks when displaying
| linebegin | --line prefix, which indicates the approval of a department
| lineend |-line suffix, which indicates that the approval of a department is over
bmbegin-the department prefix, which indicates the approval department
| bmend |-Department suffix, which indicates the approval department
ygbmbegin | --Employee prefix, which represents the employees of the approved department
ygbmend | --Employee suffix, which means the employee of the approval department

fanfan1980

Use the replace () function

caoyuezj

Example
The following example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE ('abcdefghicde', 'cde', 'xxx')
GO

Here is the result set:

------------
abxxxfghixxx
(1 row (s) affected)

fanfan1980

declare @str varchar (100)
set @ str = '| bmbegin | 001 | bmend |'
select replace (replace (@str, '| bmbegin |', ''), '| bmend |', '')

--result
001

(1 row (s) affected)

caoyuezj

Thanks, 2 left
2.How to judge that all approvals have been completed
3.How to delete an approval message

fanfan1980

This table structure is not easy to do

caoyuezj

The logo of a line is already there, just delete this line, is there a way?

caoyuezj

If you find 001, you also find the lineend of 001, and then you can find linebegin. Is there a way?