SQL 从选择查询的列数据中删除特定字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13034510/
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
Remove specific characters from column data of select query
提问by user1768816
I have searched and searched for a solution to my problem and even though I've found some that may help, I'm not sure it's the right way to do it for what I need. Keep in mind I am learning and fairly new to SQL. But I have two tables I want to query using an inner join.
我已经搜索并搜索了我的问题的解决方案，即使我找到了一些可能有帮助的方法，但我不确定这是满足我需要的正确方法。请记住，我正在学习 SQL，而且还很陌生。但是我有两个表要使用内部联接进行查询。
There is a column named CAVITYin both tables but they hold the values in different formats. One table holds the value as H02 and the other table hold the value as just 2.
CAVITY两个表中都有一个列名，但它们以不同的格式保存值。一张表将值保存为 H02，而另一张表将值保存为 2。
I want to inner join on this column using the H02 format but don't want to UPDATE the table that holds the value as a single number(for data entry purposes). So for example, if the one table column has H02 and the other has 2, I want it to be joined. If one table has H13 and the other 13, I want it to be a join as well. So basically, I am wanting to remove the H and 0 (but only if there is a 0 that directly follows the H). Unless there is a way to do the opposite and add the H / H0 from the results of the other table.
我想使用 H02 格式在此列上进行内部联接，但不想更新将值保存为单个数字的表（用于数据输入目的）。因此，例如，如果一个表列有 H02 而另一个有 2，我希望它被加入。如果一个表有 H13 而另一个是 13，我希望它也是一个连接。所以基本上，我想删除 H 和 0（但前提是有一个 0 直接跟在 H 后面）。除非有相反的方法并从另一个表的结果中添加 H / H0。
example data
table1.cavity table2.cavity
H01 = 1
H02 = 2
H10 = 10
H12 = 12
It doesn't matter to me if my query results are the H0 version or not; I just need the joins to work; I can accommodate for either result in the end.
我的查询结果是不是H0版本对我来说无关紧要；我只需要连接工作；我最终可以适应任何一种结果。
I am using SQL Server 2005
我正在使用 SQL Server 2005
I have the rest of the query and it works ok; I just need this extra join added. Please help! Any help is GREATLY APPRECIATED. Thank you
我有其余的查询，它工作正常；我只需要添加这个额外的连接。请帮忙！任何帮助是极大的赞赏。谢谢
采纳答案by Mitch Satchwell
REPLACE(REPLACE(cavity,'H0',''),'H','')
REPLACE(REPLACE(cavity,'H0',''),'H','')
The first removes H0:
第一个删除 H0：
REPLACE(cavity,'H0','')
REPLACE(cavity,'H0','')
And then the second replaces any remaining H's:
然后第二个替换任何剩余的 H：
REPLACE(output_of_previous,'H','')
REPLACE(output_of_previous,'H','')
回答by Gordon Linoff
One way you can do this is as follows:
您可以这样做的一种方法如下：
from table1 join
table2
on cast(substring(table1.cavity, 2, 100) as int) = table2.cavity
(This is assuming the 2means that table2.cavity is stored as an integer.
（这是假设2table2.cavity 存储为整数的意思。
A big caution, though. You need to pay attention to the query plan. When writing such queries, it is quite easy to end up with a plan using a nested loop join. If your tables are small, this isn't a big deal. However, even moderately sized tables could produce a highly inefficient query plan.
不过，这是一个很大的警告。您需要注意查询计划。在编写此类查询时，很容易以使用嵌套循环连接的计划结束。如果你的桌子很小，这没什么大不了的。然而，即使是中等大小的表也可能产生非常低效的查询计划。
回答by Aleksandr Fedorenko
CREATE FUNCTION [dbo].[RemoveAlphaCharacters](@Temp VarChar( 1000))
RETURNS int
AS
BEGIN
WHILE PatIndex ('%[^0-9]%', @Temp) > 0
SET @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')
RETURN @Temp
END
It's possible use in join clause like this
可以在这样的 join 子句中使用
SELECT a.col1 a, b.col1 b
FROM tablea a JOIN tableb b ON dbo.RemoveAlphaCharacters(a.col1) = b.col1
