本文介绍了执行不区分空格的查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我正在尝试根据 空白不敏感的比较来编写一个将连接行的查询。例如,如果一行具有值 ''a< space> b''而另一行具有值''< space>< space> b''我希望它们是 被认为是相等的(但与''ab'不同) 如果有帮助,我很乐意做一些T-SQL。此外,我在相关列上有一个全文索引 ,但请注意我正在比较每个 其他行,而不是固定字符串。 这是一次性的工作,所以如果没有明显的方法在 数据库上做,我只会bcp out数据并敲掉一些perl或者什么 来做。所有其他条件相同我宁愿在数据库上做到这一点 虽然。 非常感谢 Andy 解决方案 如果你想在数据库中使用它,并假设你有SQL2000,那么 一种可能性就是编写一个用户定义的函数来迭代字符串 并使用标准的 字符串函数将所有空格字符缩减为单个空格。如果你只有空格(即没有标签或换行符),那么 甚至一些嵌套的REPLACE()函数也可以工作。 另一种方法是写使用sp_OA%过程实例化Regex COM 对象的函数,并使用常规的 表达式执行相同的操作。但是这两种方法都会非常慢(除非你使用你的UDF作为计算列并将其编入索引),而第二种方法 需要sysadmin权限。 /> 我怀疑,你的BCP和Perl方法可能是 最快最简单的解决方案。 西蒙 Hi,I am trying to concoct a query that will join rows on the basis of awhitespace insensitive comparison. For instance if one row has the value''a<space>b'' and another has the value ''a<space><space>b'' I want them to beconsidered equal (but not the same as ''ab'')I am happy to do some T-SQL if that helps. Also I have a full-text index onthe column in question, but note that I am comparing rows against eachother, not to a fixed string.This is for a one-off job, so if there is no obvious way to do it on thedatabase, I will just bcp out the data and knock up some perl or somethingto do it. All other things being equal I would rather do it on the databasethough.Many thanksAndy 解决方案If you want to it in the database, and assuming that you have SQL2000, thenone possibility is to write a user-defined function to iterate over a stringand reduce all whitespace characters to a single space, using the standardstring functions. If you only have spaces (ie no tabs or newlines), theneven some nested REPLACE() functions might work.Another way would be to write a function which instantiates the Regex COMobject using the sp_OA% procedures, and does the same thing with a regularexpression. But both these approaches would be very slow (unless perhaps youused your UDF as a computed column and indexed it), and the second onerequires sysadmin permissions.I suspect, though, that your BCP and Perl approach would probably be thequickest and easiest solution.Simon 这篇关于执行不区分空格的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-20 09:21
查看更多