20

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?

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
Xaisoft
  • 45,655
  • 87
  • 279
  • 432

4 Answers4

36

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.

George Mastros
  • 24,112
  • 4
  • 51
  • 59
  • 1
    How would I extract the numeric part only? – Xaisoft Dec 17 '08 at 16:49
  • 1
    Look at the link I provided. There is a user defined function that you can use to extract just the number part. – George Mastros Dec 17 '08 at 16:50
  • Is the 8000 you used in the link provided just an arbitrary number? – Xaisoft Dec 17 '08 at 16:55
  • 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. – George Mastros Dec 17 '08 at 16:57
  • 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) – Xaisoft Dec 17 '08 at 17:07
  • You implemented this incorrectly. To get the numbers... LEFT(SUBSTRING(GSA,PATINDEX('%[0-9]%',GSA),8000),PATINDEX('%[^0-9]%',SUBSTRING(GSA,PATINDEX('%[0-9]%',GSA), 8000) + 'X')-1) – George Mastros Dec 17 '08 at 17:12
  • 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 – Xaisoft Dec 17 '08 at 17:25
  • 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? – George Mastros Dec 17 '08 at 17:31
  • 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' – Xaisoft Dec 17 '08 at 17:33
  • 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 – George Mastros Dec 17 '08 at 17:43
  • Ok, it is because you are adding the character 'X' to the alphabetical part of the string? – Xaisoft Dec 17 '08 at 17:54
  • Yes. Exactly. It exists ONLY to save us from getting an error when the string is ALL numerics. – George Mastros Dec 17 '08 at 17:55
6

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
Stanislas Biron
  • 161
  • 1
  • 2
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.
RB.
  • 36,301
  • 12
  • 91
  • 131
  • what if it varies in length, such as BTA10, or BTA1? – Xaisoft Dec 17 '08 at 16:32
  • The code I've given will extract the alphabetic part, as requested. If this is not what you require, please update your question :-) – RB. Dec 17 '08 at 16:33
  • ok, sorry, I see it now. What if I wanted to extract the numeric part? – Xaisoft Dec 17 '08 at 16:34
  • I got the error: Invalid length parameter passed to the RIGHT function. – Xaisoft Dec 17 '08 at 16:38
  • Yes, so how can I check for that? – Xaisoft Dec 17 '08 at 16:41
  • 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. – RB. Dec 17 '08 at 16:41
  • 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. – Xaisoft Dec 17 '08 at 16:45
1
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
Xilmiki
  • 1,453
  • 15
  • 22