The processing of TMMI SQLMULMAE-multi-valued fuzzy query

Processing of Multivalued Fuzzy Query

The so-called multi-valued fuzzy query means that multiple parameters are passed from the application program, and these parameters are split. After splitting, the key values of the split results are respectively processed by fuzzy query.

For exact matching, whether it is a single Key value or multiple Key values, it is easy to handle, and there are many alternatives.

For fuzzy queries, a single key value is also easy, i.e.…where name like '%parameter%'(don't tell me that full fuzzy efficiency is low, I'm not doing performance comparison here)

But for fuzzy processing of multiple Key values, it is not too direct. For example, in the name field, enter 'three, four, and five', and require fuzzy matching according to three, four, and five characters respectively.

Multi-valued fuzzy queries can be handled in the following way, welcome to provide a better way, thank you

CREATE TABLE [dbo]. [Student]( [S#] [varchar](50) NULL, [Sname] [varchar](50) NULL, [Ssex] [varchar](2) NULL)INSERT INTO Student VALUES (1,'Zhang San', 1)INSERT INTO Student VALUES (2,'Li Si', 1)INSERT INTO Student VALUES (3,'Wangwu', 1)INSERT INTO Student VALUES (4,'', 1)INSERT INTO Student VALUES (5,'Sunqi', 1)--Select * from Student where Sname in ('Zhang San',' Li Si')--Single-valued fuzzy is easy to handle select * from Student where Sname Like '% 5%'--Multi-valued fuzzy matching is not so direct--The so-called multi-valued fuzzy matching is that multiple values are passed in from outside. After these values are separated, each value is fuzzy query-SQL should be written to meet functional requirements and concise clarity. At first I wanted to write something about functions,--later I thought about it, and I could do this;with CTENameas( --This is a string splitting function select id,concat('%',s,'%') as name from dbo.f_splitStrToTable ('four, five, six',')select * from Studentwhere exists( select 1 from CTEName where Sname like name)--Query Results S# Sname Ssex2 Li Si 13 Wang Wu 14 Zhao Liu 1(3 row(s) affected)