How do I extract part of a string in t-sql

问题:

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

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

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

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.

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

Extracting numbers with 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.

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 :

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