SQL - converting table returned from USER DEFINED FUNCTION to array

Question

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

SplitTextReturnTable('Emmanuel John Ahmed', ' ')

// returns a table containing three rows having Emmanuel, John and 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.

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.

I see you have edited to say that your function does actually return 3 rows. In that case you just need to join as in my answer. — Martin Smith
– Martin Smith, Commented Jan 20, 2011 at 12:22

Martin Smith · Accepted Answer · 2011-01-19 11:09:03Z

3

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

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.

edited Jan 19, 2011 at 11:09

answered Jan 19, 2011 at 11:04

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tobechukwu Ezenachukwu Over a year ago

thanks Martin.Is there any way to use FullTextSearch with SQL SERVER 2005 EXPRESS Edition? Any link to resources and tutorials would be appreciated. Thanks

Martin Smith Over a year ago

@Tobechukwu - Yes you can. You need to download the version with Advanced Services though. It doesn't come bundled with the other edition. I don't know of any particularly good resources in this area but its quite easy to set up full text indexing via a wizard and google should have plenty of stuff.

Collectives™ on Stack Overflow

SQL - converting table returned from USER DEFINED FUNCTION to array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related