Transact-SQL：如何标记字符串？

I want to be 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:

我希望能够从文本框中标记化输入字符串以进行查询。示例:用户在文本框中输入“abc xyz 123”。我想做这个:

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.

4 个解决方案

#1

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 + '%'

#2

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

一种可能的解决方案是,您获取字符串并将其拆分为您的标记,并将每个标记插入临时表,然后将临时表连接到搜索表,并在连接中执行类似'%'+ tokenColumn +' %'获取包含令牌值的所有行

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

以下是拆分字符串的示例:http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

#3

You can use dynamic SQL to achieve this:

您可以使用动态SQL来实现此目的:

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%'

不是最完美的解决方案。但是,如果某人输入'drop table Test1',查询将是SELECT * FROM Test1 WHERE名称如'%drop%'或名称如'%table%'或名称如'%Test1%'

#4

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.

我也有同样的问题。我有1,200行字符串用空格字符分割。我的答案是使用像Open Office Calc这样的电子表格。然后,如果需要将它们放在db中,可以通过公式生成插入脚本。这不会使用SQL,但它可能像我一样解决你的问题。

#1