Finding last 3 numbers in a string 1

[jadams0173]

Using sql 2000

I have a table that has a list of items in it. The items consist of samples like:

PR10232A RETURN 232
123456A RETURN 456
65498566ABC RETURN 566
65AD123456AB RETURN 456

All I've manage to do is confuse myself.

 

[gmmastros]

I would reverse the string and then look for the first Non-Numeric character.... like this example:

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('PR10232A       RETURN 232')
Insert Into @Temp Values('123456A        RETURN 456')
Insert Into @Temp Values('65498566ABC    RETURN 566')
Insert Into @Temp Values('65AD123456AB   RETURN 456')
Insert Into @Temp Values('65AD123456AB   NO RETURN')

Select Data, Right(Data, PatIndex('%[^0-9]%',Reverse(Data))-1)
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fxthoth]

Hi. It really depends on what values can be there...

Try this:

create table example(
	foo varchar(100)
)

insert into example values('PR10232A')
insert into example values('123456A')
insert into example values('65498566ABC')
insert into example values('65AD123456AB')

select reverse(substring(reverse(foo),patindex('%A%',reverse(foo))+1,3)) as new_foo
	,foo
from example

 

[gmmastros]

Ah..... Sorry....

This code fails if you have an empty string in the data. It's easy enough to accommodate though.

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('PR10232A       RETURN 232')
Insert Into @Temp Values('123456A        RETURN 456')
Insert Into @Temp Values('65498566ABC    RETURN 566')
Insert Into @Temp Values('65AD123456AB   RETURN 456')
Insert Into @Temp Values('65AD123456AB   NO RETURN')
[!]Insert Into @Temp Values('')[/!]

Select Right(Data, PatIndex('%[^0-9]%',Reverse([!]'x' +[/!] Data))-1)
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I think I may have misunderstood the question.

I think you should take a look at my blog describing something very similar.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server

The only difference is... the blog is searching for numbers from the beginning. By using the reverse function in appropriate places, you should be able to get this to work for you.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Hey all. George, I will go read that blog.

 

[jadams0173]

I'm back! Hey George. It looks as though that will work with some modifications. Sad part is I had already read this once and commented on it. For some reason I didn't think about it yesterday when I was trying to come up with a solution.

 

[jadams0173]

This worked for 95% of my data. I've still got some unique strings that this doesn't work for. It's better than I was doing so I'll live with it for now. Thanks again to all for there help!