Transact-SQL: How do I tokenize a string?

I want to b开发者_JS百科e able to tokenize an input string from a text box to do my query. Example: user enters "abc xyz 123" in the text box. I want to do this:

SELECT * FROM database WHERE Name contains "abc" AND "xyz" AND "123"
-- as opposed to containing "abc xyz 123"
-- please ignore my sql syntax, I am an absolute beginner

Thanks.

Using a string split function (for example, like this one), you could have something like this:

SELECT t.*
FROM atable t
  INNER JOIN dbo.Split(@UserInput, ' ') s ON t.Name LIKE '%' + s.Data + '%'

One possible solution is that you take the string and split it into your tokens and the for each token insert it into a temp table and then join you temp table to your search table and in the join do a like '%' + tokenColumn + '%' to get all your rows that contain a value from your tokens

Here's an example of splitting the string: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

You can use dynamic SQL to achieve this:

declare @InputText varchar(8000)
set @InputText = 'abc xyz 123'

declare @CommorText varchar(8000)
set @CommorText = 'SELECT * FROM table WHERE Name like ''%' + replace(@InputText, ' ', '%'' or Name like ''%') + '%''';

-- @CommorText would look like this at this point
-- SELECT * FROM table WHERE Name like '%abc%' or Name like '%xyz%' or Name like '%123%'

execute(@CommorText)

[Edit] In response to Avitus's comment:

Not the most perfect solution. However, If somebody enters 'drop table Test1', query will be SELECT * FROM Test1 WHERE Name like '%drop%' or Name like '%table%' or Name like '%Test1%'

I have the same question. I got 1,200 rows of strings to split by a space character. My answer is to use a spreadsheet like Open Office Calc. Then if you need to put them in db, you can generate insert scripts via formula. This won't use SQL but it might solve your problem like mine.