oracle SQL中最接近的部分字符串匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6343528/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
closest partial string matching in SQL
提问by Chris Gow
I have a database table that contains among other things partial postal codes. I'm trying to write a query that will take a postal code and find the row in the table that matches that code as close as possible. In the example below a Gold customer with postal code 'A1A B2E' would match the first row and a Bronze customer with the same postal code would match the third row
我有一个数据库表，其中包含部分邮政编码。我正在尝试编写一个查询，该查询将采用邮政编码并在表中找到尽可能接近该代码的行。在下面的示例中，邮政编码为“A1A B2E”的金牌客户将匹配第一行，而邮政编码相同的铜牌客户将匹配第三行
CUST_TYPE | POST_CODE | SHIPPING_SURCHARGE
------------------------------------------
Gold | A1A | 0.99
Gold | A2A | 1.01
Gold | A | 3.00
Bronze | A | 1.05
Silver | A | 1.02
Bronze | B | 1.07
In all cases the query would query by both the CUST_TYPE and the POST_CODE columns. I'd want the query to only return a single row containing the one row that best matches the postal code. So, if I query Gold and 'A1AB2B' I would want the first row (Gold, A1A, 0.99) to be returned not both the first and third rows
在所有情况下，查询都会通过 CUST_TYPE 和 POST_CODE 列进行查询。我希望查询只返回一行，其中包含与邮政编码最匹配的一行。因此，如果我查询 Gold 和 'A1AB2B' 我希望返回第一行（Gold, A1A, 0.99）而不是第一行和第三行
回答by ypercube??
SQL-Server
SQL服务器
SELECT TOP (1)
*
FROM yourTable
WHERE CUST_TYPE = @cust_type
AND POST_CODE = LEFT( @postal_code, LEN(POST_CODE) )
ORDER BY LEN(POST_CODE) DESC
Oracle
甲骨文
(I can't test now:)
（我现在无法测试：）
SELECT
*
FROM
( SELECT
*
FROM yourTable
WHERE CUST_TYPE = @cust_type
AND POST_CODE = SUBSTR( @postal_code, 1, LENGTH(POST_CODE) )
ORDER BY LENGTH(POST_CODE) DESC
)
WHERE rownum = 1
回答by UltraCommit
You have simply to write a condition as the following:
您只需编写如下条件：
SELECT * FROM YOUR_TABLE
WHERE CUST_TYPE = Variable_with_cust_type AND
Variable_with_postal_code like '%'||POST_CODE||'%'
You simply concat '%' at the beginning and at the end of the field POST_CODE, and then use the like operator.
您只需在 POST_CODE 字段的开头和结尾连接 '%'，然后使用 like 运算符。
I am assuming you are using ORACLE PL/SQL.
我假设您使用的是 ORACLE PL/SQL。
