replace problem

Hi,
We hae a varchar column that has data values stored like:
'abc645abc56'
'adc015adc56'
and we need to extract only the numeric values from this
column for example for above 2 rows:
'64556'
'01556'
i am trying to use replace but is not allowing me to use
patters like select replace(column,'[A-Z]','')
How to use replace or any other function to solve this ?
Thanks
--HarvinderCheck each character and see if is numeric if not dump it.
Here is a function that I use. Run the script on the server in the right
database. To call it just do:
select StripNonNumeric(fieldname) from tablename
CREATE FUNCTION [dbo].[StripNonNumeric] (@.strIn varchar(30))
RETURNS FLOAT AS
BEGIN
declare @.intCounter int
declare @.strTmp varchar(300)
declare @.chrTmp varchar(1)
set @.intCounter = 1
set @.strTmp = ''
WHILE @.intCounter <Len(@.strIn)+1
BEGIN
set @.chrTmp = substring(@.strIn,@.intCounter,1)
if @.chrTmp NOT LIKE '%[^0123456789]%'
begin
set @.strTmp = @.strTmp + @.chrTmp
End
set @.intCounter =@.intCounter +1
if @.strTmp = ''
begin
set @.strTmp = null
end
END
return @.strTmp
END
"harvinder" <hs@.metratech.com> wrote in message
news:50d801c3417c$b99844a0$a401280a@.phx.gbl...
> Hi,
> We hae a varchar column that has data values stored like:
> 'abc645abc56'
> 'adc015adc56'
> and we need to extract only the numeric values from this
> column for example for above 2 rows:
> '64556'
> '01556'
> i am trying to use replace but is not allowing me to use
> patters like select replace(column,'[A-Z]','')
> How to use replace or any other function to solve this ?
> Thanks
> --Harvinder
>|||is yours better than mine?
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OS1YIIYQDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Nothing built in using *just* replace, but you could create a function and
> call that inline:
>
> CREATE FUNCTION dbo.makeNumeric
> (
> @.f VARCHAR(32)
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.p TINYINT
> SET @.p = PATINDEX('%[^0-9]%', @.f)
> WHILE @.p > 0
> BEGIN
> SET @.f = STUFF(@.f, @.p, 1, '')
> SET @.p = PATINDEX('%[^0-9]%', @.f)
> END
> RETURN @.f
> END
> GO
> SELECT dbo.makeNumeric('abc645abc56')
> GO
> DROP FUNCTION dbo.makeNumeric
> GO
>
> "harvinder" <hs@.metratech.com> wrote in message
> news:50d801c3417c$b99844a0$a401280a@.phx.gbl...
> > Hi,
> >
> > We hae a varchar column that has data values stored like:
> > 'abc645abc56'
> > 'adc015adc56'
> >
> > and we need to extract only the numeric values from this
> > column for example for above 2 rows:
> > '64556'
> > '01556'
> >
> > i am trying to use replace but is not allowing me to use
> > patters like select replace(column,'[A-Z]','')
> > How to use replace or any other function to solve this ?
> >
> > Thanks
> > --Harvinder
> >
>|||What does "better" mean?
I have no idea. I really haven't looked at yours.
If you are slyly asking why I posted a function when you already had, I
didn't see your post yet until I refreshed my list, *after* I had posted
mine.
"Tammy B." <Tb@.stinkylips.com> wrote in message
news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> is yours better than mine?|||No i wasn't asking anything slyly, I was asking you to look at mine.
If yours is better then I will use yours.
You are the MVP.
get some rest
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:edMVqdYQDHA.560@.TK2MSFTNGP10.phx.gbl...
> What does "better" mean?
> I have no idea. I really haven't looked at yours.
> If you are slyly asking why I posted a function when you already had, I
> didn't see your post yet until I refreshed my list, *after* I had posted
> mine.
>
>
> "Tammy B." <Tb@.stinkylips.com> wrote in message
> news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> > is yours better than mine?
>|||better means it works better. You should get some rest and look it up.
The isnumeric part can be tricky because $ can be numeric with isnumeric and
I went through several versions, but I don't have an MVP that I like to tack
on to my posts.
I thought you might have a better version.
Gee It never occured to me that you might have posted something at the same
time as me. This is my first newsgroup. This is my first moment on earth.
Thanks for pointing stuff out to me.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:edMVqdYQDHA.560@.TK2MSFTNGP10.phx.gbl...
> What does "better" mean?
> I have no idea. I really haven't looked at yours.
> If you are slyly asking why I posted a function when you already had, I
> didn't see your post yet until I refreshed my list, *after* I had posted
> mine.
>
>
> "Tammy B." <Tb@.stinkylips.com> wrote in message
> news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> > is yours better than mine?
>|||You are an idiot
"Tammy B." <Tb@.stinkylips.com> wrote in message
news:uDHwHoZQDHA.1552@.TK2MSFTNGP10.phx.gbl...
> better means it works better. You should get some rest and look it up.
> The isnumeric part can be tricky because $ can be numeric with isnumeric
and
> I went through several versions, but I don't have an MVP that I like to
tack
> on to my posts.
> I thought you might have a better version.
> Gee It never occured to me that you might have posted something at the
same
> time as me. This is my first newsgroup. This is my first moment on
earth.
> Thanks for pointing stuff out to me.
>
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:edMVqdYQDHA.560@.TK2MSFTNGP10.phx.gbl...
> > What does "better" mean?
> >
> > I have no idea. I really haven't looked at yours.
> >
> > If you are slyly asking why I posted a function when you already had, I
> > didn't see your post yet until I refreshed my list, *after* I had posted
> > mine.
> >
> >
> >
> >
> > "Tammy B." <Tb@.stinkylips.com> wrote in message
> > news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> > > is yours better than mine?
> >
> >
>|||How can you prove that you don't know what "better" means by giving me
definitions of "better".
what an idiot.
I explained the context in which I wanted to find code that may be better
than mine. If it is better in other ways, so much the better you idiot.
I said that the isnumeric part can be tricky because $ can be numeric with
isnumeric.
If the code is better in this respect I would use it in place of mine.
There has been much debate about this issue - none of which you are aware
of; the discussion was far boader than what you covered in your MVP
workbook.
Yes, why don't you get back to me with how your code stacks up according to
each of the definitions that you have provided.
I'll be waiting up.
You should not represent yourself as being valuable to Microsoft; when you
are just poo-pooing in the pool.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23OVGFxZQDHA.2424@.tk2msftngp13.phx.gbl...
> > better means it works better.
> I still don't understand what "works better" means. Less code? Less
> complicated code? Faster? More reliable? Covers more borderline cases?
> Meets this specific poster's needs?
> > but I don't have an MVP that I like to tack
> > on to my posts.
> It's not something I tack on to my posts. It's something I earned. Look
it
> up. http://mvp.support.microsoft.com/
>|||> what an idiot.
Welcome to my killfile. I don't know what I did to so greatly offend you,
but if all you can do is call me names, I have absolutely no interest in
carrying on a conversation with you. Have a great weekend.