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

Question

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?

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

Solution 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

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

Solution 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

View more solutions

Comments

[Vishal_Kotecha]

what if it varies in length, such as BTA10, or BTA1?

[Maurício]

The code I've given will extract the alphabetic part, as requested. If this is not what you require, please update your question :-)

[Vishal_Kotecha]

ok, sorry, I see it now. What if I wanted to extract the numeric part?

[Vishal_Kotecha]

I got the error: Invalid length parameter passed to the RIGHT function.

[Vishal_Kotecha]

Yes, so how can I check for that?

[Maurício]

One of your strings does not start with 'BTA'. I've added a check for that, but really "G Mastros" answer is a more complete one than mine.

[Vishal_Kotecha]

You are correct, the name of the column is called GSA, so I did a RIGHT(GSA, LEN(GSA) - 3), but as you pointed out, not all the GSA's have a length of 3 which would result in a negative value for the second parameter of the RIGHT function.

[Vishal_Kotecha]

How would I extract the numeric part only?

[George Mastros]

Look at the link I provided. There is a user defined function that you can use to extract just the number part.

[Vishal_Kotecha]

Is the 8000 you used in the link provided just an arbitrary number?

[George Mastros]

I used 8000 because that is the largest length that you can use for a varchar column. I decided to use varchar(8000) instead of varchar(max) so that the function could be used with SQL2000. If you are using SQL2005 (or above), you can change the 8000 to max.

[Vishal_Kotecha]

I tried this to extract the number, but it returned blanks: LEFT(SubString(GSA,PatIndex('%[0-9]%',GSA),8000),PatIndex('% [0-9]%',SubString(GSA,PatIndex('%[0-9]%',GSA),8000) + '1') - 1)

[George Mastros]

You implemented this incorrectly. To get the numbers... LEFT(SUBSTRING(GSA,PATINDEX('%[0-9]%',GSA),8000),PATINDEX('%‌​[^0-9]%',SUBSTRING(G‌​SA,PATINDEX('%[0-9]%‌​',GSA), 8000) + 'X')-1)

[Vishal_Kotecha]

Ok, great that worked. Thanks for the help so far, 2 questions: What does the 'X' do, why can't I just put '1' for example and what does the ^ symbol do in '%[^0-9]%'? Thanks again

[George Mastros]

The ^ in the search means NOT. %[^0-9]% looks for the first character that is NOT 0 to 9. You need to make sure PatIndex does not return 0. If PatIndex does not find a match, it returns 0. For this function, it would be better to return the length of the string, hence the + 'X'. Make sense?

[Vishal_Kotecha]

The ^ part does, but is 'X' a predefined keyword in PatIndex? I'm not sure why you can't just use '1' instead of 'X'

[George Mastros]

Run this: Select PatIndex('%[^0-9]%', '123')... PatIndex returns 0. Next, we use a left function with PatIndex - 1. If PatIndex returns 0, we end up with Left(Data, PatIndex-1) or Left(Data, -1), which would cause an error. By hardcoding something that matches, we guarantee the left function works

[Vishal_Kotecha]

Ok, it is because you are adding the character 'X' to the alphabetical part of the string?

[George Mastros]

Yes. Exactly. It exists ONLY to save us from getting an error when the string is ALL numerics.