Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Strip out alphas,leave numerics in a string

8 posts in General Discussion Last posting was on 2011-12-14 16:31:47.0Z
Ericrad Posted on 2011-12-13 20:37:58.0Z
Sender: 7851.4ee7b77f.1804289383@sybase.com
From: ericrad
Newsgroups: sybase.public.ase.general
Subject: Strip out alphas,leave numerics in a string
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ee7b7a6.786b.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Dec 2011 12:37:58 -0800
X-Trace: forums-1-dub 1323808678 10.22.241.41 (13 Dec 2011 12:37:58 -0800)
X-Original-Trace: 13 Dec 2011 12:37:58 -0800, 10.22.241.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30760
Article PK: 73652

Hi,

I have a request from one of our developers to add a new
column to a table and to populate it with the digits only
from an existing column.

example:
table.colum1 = 'This is 2112 and the other numbers are 46
and 2'

After the fact I want to be able to store the following into
the new column
2112462

I looked at str_replace, stuff and patindex but nothing
seems to give me what I'm looking for. I end up with either
NULL or the full text of the original column in the new.

Thanks
eric


Rob V Posted on 2011-12-13 21:54:37.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Strip out alphas,leave numerics in a string
References: <4ee7b7a6.786b.1681692777@sybase.com>
In-Reply-To: <4ee7b7a6.786b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ee7c99d$1@forums-1-dub>
Date: 13 Dec 2011 13:54:37 -0800
X-Trace: forums-1-dub 1323813277 10.22.241.152 (13 Dec 2011 13:54:37 -0800)
X-Original-Trace: 13 Dec 2011 13:54:37 -0800, vip152.sybase.com
Lines: 73
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30761
Article PK: 73651


On 13-Dec-2011 21:37, ericrad wrote:
> Hi,
>
> I have a request from one of our developers to add a new
> column to a table and to populate it with the digits only
> from an existing column.
>
> example:
> table.colum1 = 'This is 2112 and the other numbers are 46
> and 2'
>
> After the fact I want to be able to store the following into
> the new column
> 2112462
>
> I looked at str_replace, stuff and patindex but nothing
> seems to give me what I'm looking for. I end up with either
> NULL or the full text of the original column in the new.
>
> Thanks
> eric

If you are in 15.0.2 or later, you can write a SQL function to
encapsulate this functionality.
Create a loop around str_replace() which loops over all ascii characters
and deletes them from the source string except when it's a number as below.
If you're not on 15.0.2 you can still use this loop but you cannot pack
it into a function.

create function delete_non_digits (@v varchar(100)) returns varchar(100)
as
begin
declare @i int
set @i = 0
while @i < 256
begin
if @i not between 48 and 57
set @v = str_replace(@v, char(@i), null)
set @i = @i + 1
end
return @v
end
go
declare @v varchar(100)
set @v = 'This is 2112 and the other numbers are 46 and 2'
set @v = dbo.delete_non_digits(@v)
select @v
go

---------------
2112462

(1 row affected)

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Ericrad Posted on 2011-12-13 22:16:13.0Z
Sender: 7a3f.4ee7ba8c.1804289383@sybase.com
From: ericrad
Newsgroups: sybase.public.ase.general
Subject: Re: Strip out alphas,leave numerics in a string
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ee7cead.7c5.1681692777@sybase.com>
References: <4ee7c99d$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Dec 2011 14:16:13 -0800
X-Trace: forums-1-dub 1323814573 10.22.241.41 (13 Dec 2011 14:16:13 -0800)
X-Original-Trace: 13 Dec 2011 14:16:13 -0800, 10.22.241.41
Lines: 96
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30762
Article PK: 73654

Hello,

Thank you so much, that worked perfectly on our 15.5
server. As you noted non 15.0.x I will have to use this
outside of a function. How would I do that in an update
command?

update table
set newcolumn = dbo.delete_non_digits(original_column)

is the 15.x version as a function.

Not sure about the 12.5.x version.

Thanks again,
Eric

> On 13-Dec-2011 21:37, ericrad wrote:
> > Hi,
> >
> > I have a request from one of our developers to add a
> > new column to a table and to populate it with the digits
> > only from an existing column.
> >
> > example:
> > table.colum1 = 'This is 2112 and the other numbers are
> > 46 and 2'
> >
> > After the fact I want to be able to store the following
> > into the new column
> > 2112462
> >
> > I looked at str_replace, stuff and patindex but nothing
> > seems to give me what I'm looking for. I end up with
> > either NULL or the full text of the original column in
> the new. >
> > Thanks
> > eric
>
> If you are in 15.0.2 or later, you can write a SQL
> function to encapsulate this functionality.
> Create a loop around str_replace() which loops over all
> ascii characters and deletes them from the source string
> except when it's a number as below. If you're not on
> 15.0.2 you can still use this loop but you cannot pack it
> into a function.
>
> create function delete_non_digits (@v varchar(100))
> returns varchar(100) as
> begin
> declare @i int
> set @i = 0
> while @i < 256
> begin
> if @i not between 48 and 57
> set @v = str_replace(@v, char(@i), null)
> set @i = @i + 1
> end
> return @v
> end
> go
> declare @v varchar(100)
> set @v = 'This is 2112 and the other numbers are 46 and 2'
> set @v = dbo.delete_non_digits(@v)
> select @v
> go
>
> ---------------
> 2112462
>
> (1 row affected)
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>


Rob V Posted on 2011-12-13 22:23:30.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Strip out alphas,leave numerics in a string
References: <4ee7c99d$1@forums-1-dub> <4ee7cead.7c5.1681692777@sybase.com>
In-Reply-To: <4ee7cead.7c5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ee7d062$1@forums-1-dub>
Date: 13 Dec 2011 14:23:30 -0800
X-Trace: forums-1-dub 1323815010 10.22.241.152 (13 Dec 2011 14:23:30 -0800)
X-Original-Trace: 13 Dec 2011 14:23:30 -0800, vip152.sybase.com
Lines: 123
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30763
Article PK: 73653

In pre-15.0.2 you'd either run the update in a loop, or retrieve the
value into a variable first, run that through the loop, and then update
the row with the final value of the variable.


HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

On 13-Dec-2011 23:16, ericrad wrote:
> Hello,
>
> Thank you so much, that worked perfectly on our 15.5
> server. As you noted non 15.0.x I will have to use this
> outside of a function. How would I do that in an update
> command?
>
> update table
> set newcolumn = dbo.delete_non_digits(original_column)
>
> is the 15.x version as a function.
>
> Not sure about the 12.5.x version.
>
> Thanks again,
> Eric
>
>
>> On 13-Dec-2011 21:37, ericrad wrote:
>>> Hi,
>>>
>>> I have a request from one of our developers to add a
>>> new column to a table and to populate it with the digits
>>> only from an existing column.
>>>
>>> example:
>>> table.colum1 = 'This is 2112 and the other numbers are
>>> 46 and 2'
>>>
>>> After the fact I want to be able to store the following
>>> into the new column
>>> 2112462
>>>
>>> I looked at str_replace, stuff and patindex but nothing
>>> seems to give me what I'm looking for. I end up with
>>> either NULL or the full text of the original column in
>> the new.>
>>> Thanks
>>> eric
>> If you are in 15.0.2 or later, you can write a SQL
>> function to encapsulate this functionality.
>> Create a loop around str_replace() which loops over all
>> ascii characters and deletes them from the source string
>> except when it's a number as below. If you're not on
>> 15.0.2 you can still use this loop but you cannot pack it
>> into a function.
>>
>> create function delete_non_digits (@v varchar(100))
>> returns varchar(100) as
>> begin
>> declare @i int
>> set @i = 0
>> while @i< 256
>> begin
>> if @i not between 48 and 57
>> set @v = str_replace(@v, char(@i), null)
>> set @i = @i + 1
>> end
>> return @v
>> end
>> go
>> declare @v varchar(100)
>> set @v = 'This is 2112 and the other numbers are 46 and 2'
>> set @v = dbo.delete_non_digits(@v)
>> select @v
>> go
>>
>> ---------------
>> 2112462
>>
>> (1 row affected)
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>


"Mark A. Parsons" <iron_horse Posted on 2011-12-14 02:47:05.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Strip out alphas,leave numerics in a string
References: <4ee7c99d$1@forums-1-dub> <4ee7cead.7c5.1681692777@sybase.com> <4ee7d062$1@forums-1-dub>
In-Reply-To: <4ee7d062$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ee80e29$1@forums-1-dub>
Date: 13 Dec 2011 18:47:05 -0800
X-Trace: forums-1-dub 1323830825 10.22.241.152 (13 Dec 2011 18:47:05 -0800)
X-Original-Trace: 13 Dec 2011 18:47:05 -0800, vip152.sybase.com
Lines: 127
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30764
Article PK: 73655

Definitely want to consider pulling the initial value into a @variable, doing all the replacements via @variables and
then write the final result back to the table, otherwise the repetitive updates will generate a lot of log activity (not
to mention slow down the process due to waiting for all the log writes to complete).

On 12/13/2011 17:23, Rob V wrote:
> In pre-15.0.2 you'd either run the update in a loop, or retrieve the value into a variable first, run that through the
> loop, and then update the row with the final value of the variable.
>
>
> HTH,
>
> Rob V.
> -----------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE, IQ, Replication Server
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks& Recipes for Sybase ASE"
> "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
> Sypron B.V., The Netherlands | Chamber of Commerce 27138666
> -----------------------------------------------------------------
>
>
>
>
> On 13-Dec-2011 23:16, ericrad wrote:
>> Hello,
>>
>> Thank you so much, that worked perfectly on our 15.5
>> server. As you noted non 15.0.x I will have to use this
>> outside of a function. How would I do that in an update
>> command?
>>
>> update table
>> set newcolumn = dbo.delete_non_digits(original_column)
>>
>> is the 15.x version as a function.
>>
>> Not sure about the 12.5.x version.
>>
>> Thanks again,
>> Eric
>>
>>
>>> On 13-Dec-2011 21:37, ericrad wrote:
>>>> Hi,
>>>>
>>>> I have a request from one of our developers to add a
>>>> new column to a table and to populate it with the digits
>>>> only from an existing column.
>>>>
>>>> example:
>>>> table.colum1 = 'This is 2112 and the other numbers are
>>>> 46 and 2'
>>>>
>>>> After the fact I want to be able to store the following
>>>> into the new column
>>>> 2112462
>>>>
>>>> I looked at str_replace, stuff and patindex but nothing
>>>> seems to give me what I'm looking for. I end up with
>>>> either NULL or the full text of the original column in
>>> the new.>
>>>> Thanks
>>>> eric
>>> If you are in 15.0.2 or later, you can write a SQL
>>> function to encapsulate this functionality.
>>> Create a loop around str_replace() which loops over all
>>> ascii characters and deletes them from the source string
>>> except when it's a number as below. If you're not on
>>> 15.0.2 you can still use this loop but you cannot pack it
>>> into a function.
>>>
>>> create function delete_non_digits (@v varchar(100))
>>> returns varchar(100) as
>>> begin
>>> declare @i int
>>> set @i = 0
>>> while @i< 256
>>> begin
>>> if @i not between 48 and 57
>>> set @v = str_replace(@v, char(@i), null)
>>> set @i = @i + 1
>>> end
>>> return @v
>>> end
>>> go
>>> declare @v varchar(100)
>>> set @v = 'This is 2112 and the other numbers are 46 and 2'
>>> set @v = dbo.delete_non_digits(@v)
>>> select @v
>>> go
>>>
>>> ---------------
>>> 2112462
>>>
>>> (1 row affected)
>>>
>>> --
>>> HTH,
>>>
>>> Rob V.
>>> ----------------------------------------------------------
>>> ------- Rob Verschoor
>>>
>>> Certified Sybase Professional DBA for ASE, IQ, Replication
>>> Server
>>>
>>> Author of Sybase books (order online at
>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>>> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
>>> "The Complete Sybase ASE Quick Reference Guide"
>>> "The Complete Sybase Replication Server Quick Reference
>>> Guide"
>>>
>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>>> Commerce 27138666
>>> ----------------------------------------------------------
>>> -------
>>>
>


Ericrad Posted on 2011-12-14 16:27:50.0Z
Sender: 9fb.4ee7d3c7.1804289383@sybase.com
From: Ericrad
Newsgroups: sybase.public.ase.general
Subject: Re: Strip out alphas,leave numerics in a string
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ee8ce86.30c.1681692777@sybase.com>
References: <4ee80e29$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Dec 2011 08:27:50 -0800
X-Trace: forums-1-dub 1323880070 10.22.241.41 (14 Dec 2011 08:27:50 -0800)
X-Original-Trace: 14 Dec 2011 08:27:50 -0800, 10.22.241.41
Lines: 140
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30766
Article PK: 73656

Thank you both, the function worked perfectly, and the
heads up on log activity is appreciated.

Take care,
Eric

>Definitely want to consider pulling the initial value into
> a @variable, doing all the replacements via @variables and
> then write the final result back to the table, otherwise
> the repetitive updates will generate a lot of log activity
> (not to mention slow down the process due to waiting for
> all the log writes to complete).
>
> On 12/13/2011 17:23, Rob V wrote:
> > In pre-15.0.2 you'd either run the update in a loop, or
> retrieve the value into a variable first, run that through
> > the loop, and then update the row with the final value
> of the variable. >
> >
> > HTH,
> >
> > Rob V.
> >
> ----------------------------------------------------------
> > ------- Rob Verschoor
> >
> > Certified Sybase Professional DBA for ASE, IQ,
> Replication Server >
> > Author of Sybase books (order online at
> > www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> > ASE" "The Complete Sybase IQ Quick Reference Guide"
> > (new!) "The Complete Sybase ASE Quick Reference Guide"
> > "The Complete Sybase Replication Server Quick Reference
> Guide" >
> > rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> > @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> > Commerce 27138666
> ----------------------------------------------------------
> ------- >
> >
> >
> >
> > On 13-Dec-2011 23:16, ericrad wrote:
> >> Hello,
> >>
> >> Thank you so much, that worked perfectly on our 15.5
> >> server. As you noted non 15.0.x I will have to use this
> >> outside of a function. How would I do that in an update
> >> command?
> >>
> >> update table
> >> set newcolumn = dbo.delete_non_digits(original_column)
> >>
> >> is the 15.x version as a function.
> >>
> >> Not sure about the 12.5.x version.
> >>
> >> Thanks again,
> >> Eric
> >>
> >>
> >>> On 13-Dec-2011 21:37, ericrad wrote:
> >>>> Hi,
> >>>>
> >>>> I have a request from one of our developers to add a
> >>>> new column to a table and to populate it with the
> digits >>>> only from an existing column.
> >>>>
> >>>> example:
> >>>> table.colum1 = 'This is 2112 and the other numbers
> are >>>> 46 and 2'
> >>>>
> >>>> After the fact I want to be able to store the
> following >>>> into the new column
> >>>> 2112462
> >>>>
> >>>> I looked at str_replace, stuff and patindex but
> nothing >>>> seems to give me what I'm looking for. I end
> up with >>>> either NULL or the full text of the original
> column in >>> the new.>
> >>>> Thanks
> >>>> eric
> >>> If you are in 15.0.2 or later, you can write a SQL
> >>> function to encapsulate this functionality.
> >>> Create a loop around str_replace() which loops over
> all >>> ascii characters and deletes them from the source
> string >>> except when it's a number as below. If you're
> not on >>> 15.0.2 you can still use this loop but you
> cannot pack it >>> into a function.
> >>>
> >>> create function delete_non_digits (@v varchar(100))
> >>> returns varchar(100) as
> >>> begin
> >>> declare @i int
> >>> set @i = 0
> >>> while @i< 256
> >>> begin
> >>> if @i not between 48 and 57
> >>> set @v = str_replace(@v, char(@i), null)
> >>> set @i = @i + 1
> >>> end
> >>> return @v
> >>> end
> >>> go
> >>> declare @v varchar(100)
> >>> set @v = 'This is 2112 and the other numbers are 46
> and 2' >>> set @v = dbo.delete_non_digits(@v)
> >>> select @v
> >>> go
> >>>
> >>> ---------------
> >>> 2112462
> >>>
> >>> (1 row affected)
> >>>
> >>> --
> >>> HTH,
> >>>
> >>> Rob V.
> >>>
> ----------------------------------------------------------
> >>> ------- Rob Verschoor >>>
> >>> Certified Sybase Professional DBA for ASE, IQ,
> Replication >>> Server
> >>>
> >>> Author of Sybase books (order online at
> >>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> >>> ASE" "The Complete Sybase IQ Quick Reference Guide"
> (new!) >>> "The Complete Sybase ASE Quick Reference Guide"
> >>> "The Complete Sybase Replication Server Quick
> Reference >>> Guide"
> >>>
> >>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> >>> @rob_verschoor Sypron B.V., The Netherlands | Chamber
> of >>> Commerce 27138666
> >>>
> ----------------------------------------------------------
> >>> ------- >>>
> >


"Mark A. Parsons" <iron_horse Posted on 2011-12-14 03:08:53.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Strip out alphas,leave numerics in a string
References: <4ee7b7a6.786b.1681692777@sybase.com>
In-Reply-To: <4ee7b7a6.786b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ee81345$1@forums-1-dub>
Date: 13 Dec 2011 19:08:53 -0800
X-Trace: forums-1-dub 1323832133 10.22.241.152 (13 Dec 2011 19:08:53 -0800)
X-Original-Trace: 13 Dec 2011 19:08:53 -0800, vip152.sybase.com
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30765
Article PK: 73658

Instead of getting rid of what you don't want you might try keeping what you do want:

========================
set nocount on
go
declare @v varchar(100),
@c char(1),
@v_no varchar(100)

select @v = 'This is 2112 and the other numbers are 46 and 2',
@v_no = NULL

while @v is not NULL
begin
select @c = left(@v,1),
@v = substring(@v,2,100)

if @c like '[0-9]' select @v_no = @v_no + @c
end

select @v_no
go

-------------------
2112462
========================

I'm thinking this may be a bit more efficient than performing a large volume of
str_replace()/charindex()/patindex()/<search_function>() calls ...

On 12/13/2011 15:37, ericrad wrote:
> Hi,
>
> I have a request from one of our developers to add a new
> column to a table and to populate it with the digits only
> from an existing column.
>
> example:
> table.colum1 = 'This is 2112 and the other numbers are 46
> and 2'
>
> After the fact I want to be able to store the following into
> the new column
> 2112462
>
> I looked at str_replace, stuff and patindex but nothing
> seems to give me what I'm looking for. I end up with either
> NULL or the full text of the original column in the new.
>
> Thanks
> eric