January 26, 2006 at 10:37 am
Need more information.
are the characters you indicate by an x, are they always Alpha characters? not numbers, if so its easy.
If not, then how would you know what numbers to pull out.
Pleas let us know.
January 26, 2006 at 10:40 am
They are all characters but in some rows will be having only one number. i mean xxxxxxxxxxxxxxxx(xxx-5xx-xxxx-128-). something like this.
January 26, 2006 at 10:42 am
i just need to pull that three digit number. from my example above i need to pull 128
January 26, 2006 at 11:18 am
Nevermind,
its still very easy.
-- drop table #t1
create table #t1 (pk int identity, Field varchar(100))
insert into #t1 (Field)
select 'xxxxxxxxxxxxxx(xxx-xxx-xxx-120-xxx)' union
select 'xxxxxxxxxxxxxxxxxx(xxx-xxx-xxx-xxx-455-x)' union
select 'xxxxxxxxxxxxxxxx(xxx-5xx-xxxx-128-).'union
select 'xxxxx66xxxxxxxx(x6x-5xx-5xxx-333-)' union
select 'xxxxx66xxxxxxxx(x6x-5xx-5xxx-023-)'
select substring(Field,patindex('%[0-9][0-9][0-9]%', Field),3)
from #t1
Returns
023
333
120
128
Note this only pulls numbers where there are 3 consecutive numbers.
So if any row has 3 consecutive numbers that are not the number you want it will fail.
January 26, 2006 at 11:53 am
Hey thanks a lot for your solution. It worked fine. but have one more question. if i dont have any numbers in the data ex: i just have PAxxxx(xxx-xxx-xxx). Then its returning first two characters. The output i am getting is PA. Its working fine if i have numbers in there. is there any way that i can mention that row that has got only characters as either null or 0. Anyways Thank u so much for ur quick reply
January 26, 2006 at 12:10 pm
Oh, just put in your where clause
select substring(Field,patindex('%[0-9][0-9][0-9]%', Field),3)
from #t1
where patindex('%[0-9][0-9][0-9]%', Field) > 0
cheers
January 26, 2006 at 1:08 pm
hey!!!!!!!! that worked thank u so much for ur help.
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply