TSQL字符串匹配问题
我正在尝试使用 TSQL 匹配 2 个字符串。
第一个字符串: ABCD DFHG KLJKL
第二个字符串:ABCD DFHG KLJKL - 4536764
匹配规则:如果第二个字符串以第一个字符串开头,后跟“ - ”(即空格、破折号) ,空格)和一组数字(没有其他),将其视为匹配。
有什么想法吗?
I am trying to match 2 strings using TSQL.
First string: ABCD DFHG KLJKL
Second string: ABCD DFHG KLJKL - 4536764
Matching rule: if second string begins with the first string followed by " - " (that is, space, dash, space) and a set of numbers (and nothing else), consider it a match.
Any ideas?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
我有两个答案给你。
假设您的 FirstString 值不包含任何字符
%
、_
或[
,这将返回您的内容要求。它不仅保证第二个字符串以第一个字符串开头,后跟空格-破折号-空格和数字,还确保从该点开始仅跟随数字。如果您的表非常宽,那么包含 FirstString 和 SecondString 以及您想要选择的任何其他列(或者它们位于聚集索引中)的非聚集索引将使该索引完全覆盖查询,并且可以大大提高性能。
<前><代码>选择 *
从字符串
在哪里
SecondString LIKE FirstString + ' - [0-9]%'
并且 SecondString 与 FirstString + ' - %[^0-9]%' 不同;
我还认为,如果 FirstString 为空并且 SecondString 立即以“-”开头,那么根据规范它是正确的。
如果您的 FirstString 值确实包含上述任何字符,那么这里有一种处理方法:
<前><代码>选择*
从字符串
在哪里
左 (第二个字符串, Len(第一个字符串) + 3) = 第一个字符串 + ' - '
AND Len(SecondString) > Len(第一个字符串) + 3
AND Substring(SecondString, Len(FirstString) + 4, 2147483647) 不喜欢 '%[^0-9]%';
这是一个奇怪的领域,所以我也会尝试这个版本,看看它是否表现更好:
请注意,如果您想正确处理 FirstString 末尾的空格,可能需要进行一些调整(使用
Len
的第二个查询无法正确处理这种情况)。I have two answers for you.
Assuming that your FirstString values do not contain any of the characters
%
,_
, OR[
, this will return what you're asking for. Not only does it guarantee that the second string begins with the first and is followed by space-dash-space and a number, it also makes sure that only numbers follow from that point onward.If your table is very wide at all, then a nonclustered index that includes FirstString and SecondString as well as whatever other columns you want SELECTed (or they're in the clustered index) will make that index fully cover the query and could greatly improve performance.
I would also submit that if the FirstString is blank and SecondString starts immediately with ' - ' then it is correct per the specs.
If your FirstString value DOES contain any of the above characters, then here's one way to handle that:
This is kind of strange territory, here, so I would experiment also with this version to see if it performs any better:
Please note that if you want to properly handle spaces at the end of FirstString, some adjustment may be required (the second query which uses
Len
does not handle this case properly).这将选择包含您的字符串的任何内容,后跟 1 个空格,后跟破折号,再后跟 1 个空格,后跟任意一组数字,除了数字之外什么都没有。
编辑:要过滤掉破折号后面的任何杂乱的结果,而不仅仅是字母。
This will select anything that has your string, followed by 1 space, followed by a dash, followed by 1 more space, followed by any set of numbers, and nothing but numbers.
EDITED: To filter out results with any cruft, not just letters, following the dash.
该查询满足所有要求。
这是一个测试表,向您展示了所有测试用例
This query satisfies all the requirements.
Here's a test table, showing you all the test cases