如何在 t-sql 中提取字符串的一部分 [英] How do I extract part of a string in t-sql

问题描述

如果我有以下 nvarchar 变量 - BTA200，我怎样才能从中提取 BTA?

If I have the following nvarchar variable - BTA200, how can I extract just the BTA from it?

另外，如果我有不同的长度，例如 BTA50、BTA030，我如何只提取数字部分?

Also, if I have varying lengths such as BTA50, BTA030, how can I extract just the numeric part?

推荐答案

我会推荐 PatIndex 和 Left 的组合.精心构建，您可以编写始终有效的查询，无论您的数据是什么样的.

I would recommend a combination of PatIndex and Left. Carefully constructed, you can write a query that always works, no matter what your data looks like.

例如:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('BTA200')
Insert Into @Temp Values('BTA50')
Insert Into @Temp Values('BTA030')
Insert Into @Temp Values('BTA')
Insert Into @Temp Values('123')
Insert Into @Temp Values('X999')

Select Data, Left(Data, PatIndex('%[0-9]%', Data + '1') - 1)
From   @Temp

PatIndex 将查找 0-9 范围内的第一个字符，并返回它的字符位置，您可以将其与 LEFT 函数一起使用以提取正确的数据.请注意，PatIndex 实际上使用的是 Data + '1'.这可以保护我们免受未找到数字的数据的影响.如果没有数字，PatIndex 将返回 0.在这种情况下，LEFT 函数会出错，因为我们使用的是 Left(Data, PatIndex - 1).当 PatIndex 返回 0 时，我们最终会得到 Left(Data, -1)，它返回一个错误.

PatIndex will look for the first character that falls in the range of 0-9, and return it's character position, which you can use with the LEFT function to extract the correct data. Note that PatIndex is actually using Data + '1'. This protects us from data where there are no numbers found. If there are no numbers, PatIndex would return 0. In this case, the LEFT function would error because we are using Left(Data, PatIndex - 1). When PatIndex returns 0, we would end up with Left(Data, -1) which returns an error.

这仍然有可能失败.如需完整的解释，我鼓励您阅读:

There are still ways this can fail. For a full explanation, I encourage you to read:

使用 SQL Server 提取数字

那篇文章展示了如何从字符串中获取数字.在您的情况下，您希望获得字母字符.但是，该过程非常相似，您可能可以从中学到一些有用的东西.

That article shows how to get numbers out of a string. In your case, you want to get alpha characters instead. However, the process is similar enough that you can probably learn something useful out of it.

这篇关于如何在 t-sql 中提取字符串的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！