ansaurus

Question

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

Answer 1

+1 A:

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 2008-12-17 16:31:49

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

Xaisoft 2008-12-17 16:32:43

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

RB 2008-12-17 16:33:42

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

Xaisoft 2008-12-17 16:34:18

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

Xaisoft 2008-12-17 16:38:01

Yes, so how can I check for that?

Xaisoft 2008-12-17 16:41:31

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 2008-12-17 16:41:54

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 2008-12-17 16:45:57

Answer 2

+12 A:

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.

G Mastros 2008-12-17 16:39:29

How would I extract the numeric part only?

Xaisoft 2008-12-17 16:49:58

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

G Mastros 2008-12-17 16:50:57

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

Xaisoft 2008-12-17 16:55:51

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.

G Mastros 2008-12-17 16:57:47

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 2008-12-17 17:07:22

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)

G Mastros 2008-12-17 17:12:21

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 2008-12-17 17:25:08

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?

G Mastros 2008-12-17 17:31:12

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 2008-12-17 17:33:01

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

G Mastros 2008-12-17 17:43:00

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

Xaisoft 2008-12-17 17:54:09

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

G Mastros 2008-12-17 17:55:29

Great. Thanks for the patience.

Xaisoft 2008-12-17 17:56:45

Answer 3

+1 A:

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 2008-12-17 16:41:07

ansaurus

tags:

views:

answers:

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

related questions