如何在t-sql中提取字符串的一部分

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

如果我有下面的nvarchar变量——BTA200，我如何从中提取出BTA呢?

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

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

4 个解决方案

#1

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.

我建议合并PatIndex和Left。仔细构建后，您可以编写一个查询，该查询无论数据是什么样子，都始终有效。

Ex:

例:

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 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.

PatIndex将查找第一个在0-9范围内的字符，并返回它的字符位置，您可以使用左函数提取正确的数据。注意，PatIndex实际上使用的是Data + '1'。这就保护了我们在没有找到数字的情况下不受数据的影响。如果没有数字，PatIndex将返回0。在这种情况下，左函数会出错，因为我们使用的是左(Data, PatIndex -1)。

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

还有一些方法会失败。要获得完整的解释，我鼓励你阅读:

Extracting numbers with SQL Server

使用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.

那篇文章展示了如何从字符串中取出数字。在你的例子中，你想要得到字符。然而，这个过程非常相似，您可能可以从中学到一些有用的东西。

#2

substring(field, 1,3) will work on your examples.

子字符串(字段，1,3)将用于示例。

select substring(field, 1,3) from table

Also, if the alphabetic part is of variable length, you can do this to extract the alphabetic part:

另外，如果字母部分的长度是可变的，可以这样提取字母部分:

select substring(field, 1, PATINDEX('%[1234567890]%', field) -1) 
from table
where PATINDEX('%[1234567890]%', field) > 0

#3

LEFT ('BTA200', 3) will work for the examples you have given, as in :

左('BTA200'， 3)将适用于您提供的示例，如:

SELECT LEFT(MyField, 3)
FROM MyTable

To extract the numeric part, you can use this code

要提取数字部分，可以使用此代码

SELECT RIGHT(MyField, LEN(MyField) - 3)
FROM MyTable
WHERE MyField LIKE 'BTA%' 
--Only have this test if your data does not always start with BTA.

#4

declare @data as varchar(50)
set @data='ciao335'


--get text
Select Left(@Data, PatIndex('%[0-9]%', @Data + '1') - 1)    ---->>ciao

--get numeric
Select right(@Data, len(@data) - (PatIndex('%[0-9]%', @Data )-1) )   ---->>335

#1