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.

Spliting a character value and converting it to integer

4 posts in General Discussion Last posting was on 2011-01-07 20:33:39.0Z
sumit.kd Posted on 2011-01-07 12:57:56.0Z
Sender: 5d84.4d270997.1804289383@sybase.com
From: sumit.kd
Newsgroups: sybase.public.ase.general
Subject: Spliting a character value and converting it to integer
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d270dd4.5e25.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Jan 2011 04:57:56 -0800
X-Trace: forums-1-dub 1294405076 10.22.241.41 (7 Jan 2011 04:57:56 -0800)
X-Original-Trace: 7 Jan 2011 04:57:56 -0800, 10.22.241.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29849
Article PK: 79084

Hi,
I need to convert a varchar value like "1.4.14.5" to a
number like 1041405 (adding a zero if the number is a single
digit number. Could anybody please suggest me any way to do
this.

I am using version 15.0.3 of ASE. I tried doing it with the
below sql:

select convert(integer, str_replace(column_name, ".", null))
from database..table_name

But, it returns the value like 14145. Could anybody please
suggest what is the correct way of doing this or if there is
any way to split this value for delimiter "." and store as 4
different numbers and then concatenate.


jobless Posted on 2011-01-07 20:08:10.0Z
Sender: 7931.4d23870f.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: Spliting a character value and converting it to integer
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d2772aa.29d.1681692777@sybase.com>
References: <4d270dd4.5e25.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Jan 2011 12:08:10 -0800
X-Trace: forums-1-dub 1294430890 10.22.241.41 (7 Jan 2011 12:08:10 -0800)
X-Original-Trace: 7 Jan 2011 12:08:10 -0800, 10.22.241.41
Lines: 117
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29851
Article PK: 79077


> Hi,
> I need to convert a varchar value like "1.4.14.5" to a
> number like 1041405 (adding a zero if the number is a
> single digit number. Could anybody please suggest me any
> way to do this.
>
> I am using version 15.0.3 of ASE. I tried doing it with
> the below sql:
>
> select convert(integer, str_replace(column_name, ".",
> null)) from database..table_name
>
> But, it returns the value like 14145. Could anybody please
> suggest what is the correct way of doing this or if there
> is any way to split this value for delimiter "." and store
> as 4 different numbers and then concatenate.

> Hi,
> I need to convert a varchar value like "1.4.14.5" to a
> number like 1041405 (adding a zero if the number is a
> single digit number. Could anybody please suggest me any
> way to do this.
>
> I am using version 15.0.3 of ASE. I tried doing it with
> the below sql:
>
> select convert(integer, str_replace(column_name, ".",
> null)) from database..table_name
>
> But, it returns the value like 14145. Could anybody please
> suggest what is the correct way of doing this or if there
> is any way to split this value for delimiter "." and store
> as 4 different numbers and then concatenate.


Below code works @15.5 - i hv no other version to test the
nesting levels limitation in case it doesnt work for you -

Warning1 - Never use or write such code in DEV/UAT or PROD
Warning2 - Dont ask what this carpy code is
Warning2 - No one can maintain such code

FUN is - POWER OF TSQL



<CODE STARTS>

declare @char char(11)
select @char = '1.4.14.5'

select
str_replace(


isnull(

stuff(


isnull(stuff( isnull(stuff( @char,
patindex ("%.[0-9].[0-9]%",@char),1,'0'
),@char),
patindex ("%.[0-9].[0-9]%",isnull(stuff( @char,
patindex ("%.[0-9].[0-9]%",@char),1,'0'
),@char)),1,'0'
),isnull(stuff( @char,
patindex ("%.[0-9].[0-9]%",@char),1,'0'
),@char))


, patindex ("%.[0-9]",
(

isnull(stuff( isnull(stuff( @char,
patindex ("%.[0-9].[0-9]%",@char),1,'0'
),@char),
patindex ("%.[0-9].[0-9]%",isnull(stuff( @char,
patindex ("%.[0-9].[0-9]%",@char),1,'0'
),@char)),1,'0'
),isnull(stuff( @char,
patindex ("%.[0-9].[0-9]%",@char),1,'0'
),@char))


))
,1
,'0'
)
,
isnull(stuff( isnull(stuff( @char,
patindex ("%.[0-9].[0-9]%",@char),1,'0'
),@char),
patindex ("%.[0-9].[0-9]%",isnull(stuff( @char,
patindex ("%.[0-9].[0-9]%",@char),1,'0'
),@char)),1,'0'
),isnull(stuff( @char,
patindex ("%.[0-9].[0-9]%",@char),1,'0'
),@char))
)

,
'.',null)

<CODE ENDS>

For input '1.4.14.5' O/P is 1041405 <-- as you mentioned u
want
For input '1.14.14.5' O/P is 1141405
For input '1.14.4.5' O/P is 1140405
For input '11.4.4.5' O/P is 11040405
For input '11.14.4.5' O/P is 11140405
For input '11.4.14.5' O/P is 11041405
For input '11.14.14.5' O/P is 11141405
For input '11.14.14.15' O/P is 11141415


Mark Posted on 2011-01-07 20:33:39.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Spliting a character value and converting it to integer
References: <4d270dd4.5e25.1681692777@sybase.com> <4d2772aa.29d.1681692777@sybase.com>
In-Reply-To: <4d2772aa.29d.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: <4d2778a3$1@forums-1-dub>
Date: 7 Jan 2011 12:33:39 -0800
X-Trace: forums-1-dub 1294432419 10.22.241.152 (7 Jan 2011 12:33:39 -0800)
X-Original-Trace: 7 Jan 2011 12:33:39 -0800, vip152.sybase.com
Lines: 125
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29852
Article PK: 79078

Kudos to you jobless!

But like Rob said and you allude to in your warning, life would be much
more pleasant for the next person if the code was written in a udf and
in a maintainable way.

Have a good weekend,
Mark

On 1/7/2011 3:08 PM, jobless wrote:
>> Hi,
>> I need to convert a varchar value like "1.4.14.5" to a
>> number like 1041405 (adding a zero if the number is a
>> single digit number. Could anybody please suggest me any
>> way to do this.
>>
>> I am using version 15.0.3 of ASE. I tried doing it with
>> the below sql:
>>
>> select convert(integer, str_replace(column_name, ".",
>> null)) from database..table_name
>>
>> But, it returns the value like 14145. Could anybody please
>> suggest what is the correct way of doing this or if there
>> is any way to split this value for delimiter "." and store
>> as 4 different numbers and then concatenate.
>
>
>> Hi,
>> I need to convert a varchar value like "1.4.14.5" to a
>> number like 1041405 (adding a zero if the number is a
>> single digit number. Could anybody please suggest me any
>> way to do this.
>>
>> I am using version 15.0.3 of ASE. I tried doing it with
>> the below sql:
>>
>> select convert(integer, str_replace(column_name, ".",
>> null)) from database..table_name
>>
>> But, it returns the value like 14145. Could anybody please
>> suggest what is the correct way of doing this or if there
>> is any way to split this value for delimiter "." and store
>> as 4 different numbers and then concatenate.
>
> Below code works @15.5 - i hv no other version to test the
> nesting levels limitation in case it doesnt work for you -
>
> Warning1 - Never use or write such code in DEV/UAT or PROD
> Warning2 - Dont ask what this carpy code is
> Warning2 - No one can maintain such code
>
> FUN is - POWER OF TSQL
>
>
>
> <CODE STARTS>
>
> declare @char char(11)
> select @char = '1.4.14.5'
>
> select
> str_replace(
>
>
> isnull(
>
> stuff(
>
>
> isnull(stuff( isnull(stuff( @char,
> patindex ("%.[0-9].[0-9]%",@char),1,'0'
> ),@char),
> patindex ("%.[0-9].[0-9]%",isnull(stuff( @char,
> patindex ("%.[0-9].[0-9]%",@char),1,'0'
> ),@char)),1,'0'
> ),isnull(stuff( @char,
> patindex ("%.[0-9].[0-9]%",@char),1,'0'
> ),@char))
>
>
> , patindex ("%.[0-9]",
> (
>
> isnull(stuff( isnull(stuff( @char,
> patindex ("%.[0-9].[0-9]%",@char),1,'0'
> ),@char),
> patindex ("%.[0-9].[0-9]%",isnull(stuff( @char,
> patindex ("%.[0-9].[0-9]%",@char),1,'0'
> ),@char)),1,'0'
> ),isnull(stuff( @char,
> patindex ("%.[0-9].[0-9]%",@char),1,'0'
> ),@char))
>
>
> ))
> ,1
> ,'0'
> )
> ,
> isnull(stuff( isnull(stuff( @char,
> patindex ("%.[0-9].[0-9]%",@char),1,'0'
> ),@char),
> patindex ("%.[0-9].[0-9]%",isnull(stuff( @char,
> patindex ("%.[0-9].[0-9]%",@char),1,'0'
> ),@char)),1,'0'
> ),isnull(stuff( @char,
> patindex ("%.[0-9].[0-9]%",@char),1,'0'
> ),@char))
> )
>
> ,
> '.',null)
>
> <CODE ENDS>
>
> For input '1.4.14.5' O/P is 1041405<-- as you mentioned u
> want
> For input '1.14.14.5' O/P is 1141405
> For input '1.14.4.5' O/P is 1140405
> For input '11.4.4.5' O/P is 11040405
> For input '11.14.4.5' O/P is 11140405
> For input '11.4.14.5' O/P is 11041405
> For input '11.14.14.5' O/P is 11141405
> For input '11.14.14.15' O/P is 11141415


Rob V [ Sybase ] Posted on 2011-01-07 13:22:10.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
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: Spliting a character value and converting it to integer
References: <4d270dd4.5e25.1681692777@sybase.com>
In-Reply-To: <4d270dd4.5e25.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: <4d271382$1@forums-1-dub>
Date: 7 Jan 2011 05:22:10 -0800
X-Trace: forums-1-dub 1294406530 10.22.241.152 (7 Jan 2011 05:22:10 -0800)
X-Original-Trace: 7 Jan 2011 05:22:10 -0800, vip152.sybase.com
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29850
Article PK: 79083


On 07-Jan-2011 13:57, sumit.kd wrote:
> Hi,
> I need to convert a varchar value like "1.4.14.5" to a
> number like 1041405 (adding a zero if the number is a single
> digit number. Could anybody please suggest me any way to do
> this.
>
> I am using version 15.0.3 of ASE. I tried doing it with the
> below sql:
>
> select convert(integer, str_replace(column_name, ".", null))
> from database..table_name
>
> But, it returns the value like 14145. Could anybody please
> suggest what is the correct way of doing this or if there is
> any way to split this value for delimiter "." and store as 4
> different numbers and then concatenate.

You should not replace the '.' with NULL, sicne that only removes the
'.'. Perhaps you should replace it with '0' instead, i.e.:

select '0' + str_replace(your_column, '.' '0') from your_table

The only problem is that you 'd get '010401405', i.e. there is an extra
0 in front of '14' as well.

My gut feeling is that the only way to do this really right is to walk
down the string, searching for the next '.', and then decide if you need
to add a '0' or not. This would require some good-old loop coding.
A SQL user-defined function would be ideal for this purpose. If you'd
write a UDF named 'myfunc', then the SQL would simply be:

select dbo.myfunc(your_column) from your_table

See www.sypron.nl/udf for more info about SQL UDFs, as well as some
examples.

HTH,

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

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"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
-----------------------------------------------------------------