SQL - 将USER DEFINED FUNCTION返回的表转换为数组

I have a UDF that takes a space-delimited string and returns a table containing rows the individual strings. Something like this:

我有一个UDF,它采用空格分隔的字符串,并返回一个包含各个字符串行的表。像这样的东西:

SplitTextReturnTable('Emmanuel John Ahmed', ' ') 

// returns a table containing three rows having Emmanuel, John and Ahmed.

//返回一个包含三行Emmanuel,John和Ahmed的表。

What I want to do is use each of the returned string to perform a search in a table to retrieve rows with data matching the strings, something similar to a for-each-statement that searches the table with each of the returned strings. Here is a pseudo code of what I want.

我想要做的是使用每个返回的字符串在表中执行搜索以检索具有与字符串匹配的数据的行,类似于使用每个返回的字符串搜索表的for-each语句。这是我想要的伪代码。

DECLARE @myArray ARRAY;
SET @myArray = ConvertToArray(SplitTextReturnTable('Emmanuel John Ahmed', ' '))
SELECT * FROM Customers WHERE Customers.Fullname LIKE + '%' + @myArray[0] + '%' or Customers.Fullname LIKE + '%' + @myArray[1] + '%' or Customers.Fullname LIKE + '%' + @myArray[2] + '%'

Any help would be appreciated. I would also like to know if there's a better way to perform database searching more effectively.

任何帮助,将不胜感激。我还想知道是否有更好的方法来更有效地执行数据库搜索。

Thanks.

1 个解决方案

#1

Why does your split function only return one row for this input? It doesn't seem to be splitting at all! What you probably want it to do is to fix it to return a table with 3 rows that you can then join on. i.e. something like

为什么split函数只为此输入返回一行?它似乎根本没有分裂!您可能希望它做的是修复它以返回一个包含3行的表,然后您可以将其加入。即类似的东西

SELECT c.* 
FROM Customers c
JOIN dbo.SplitTextReturnTable('Emmanuel John Ahmed', ' ') s
ON c.Fullname LIKE '%' + s.value + '%'

Regarding the second part of your question you might want to investigate Full Text Search. LIKE with leading wildcards is inherently slow.

关于问题的第二部分,您可能需要调查全文搜索。与领先的通配符一样,本质上很慢。