Spliting a character value and converting it to integer

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.
0
sumit
1/7/2011 12:57:56 PM
sybase.ase.general 8655 articles. 0 followers. Follow

3 Replies
695 Views

Similar Articles

[PageSpeed] 24

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
-----------------------------------------------------------------

0
Rob
1/7/2011 1:22:10 PM
> 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
0
jobless
1/7/2011 8:08:10 PM
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
0
Mark
1/7/2011 8:33:39 PM
Reply:

Similar Artilces:

Error converting characters into server's character set. Some character(s) could not be converted.
Error converting characters into server's character set. Some character(s) could not be converted. No changes made to database. UPDATE Ticket_detail SET problem = 'mecho Team, I have set up several table repdefs in our test environment (PROD_jam) to determine the benefit of having table repdefs in addition to a database repdef. After I set up one of the table repdefs I ran several updates on the primary table to verify whether the where-clause was smaller on the replicate database. When I reviewed the transaction in the outbound queue on the secondary rep server (T...

Convert Character to Integer
I am trying to convert some Java code to .net.  There is a function in java Character.getNumericValue("Take a character") and it spits out a number.  It does not look like the ascii number.  I read the documentation and it said  "This method returns the Unicode numeric value of the character as a nonnegative integer" So an A is 10, B is 11 and so on and Z is 35. How do I do that in vb.net.   I really need this for my program but I am not able to get the right function. Thank you     I don't believe you got...

Convert returned value to Integer
I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.-----------------------------------------------------DECLARE @TransactionID int    <------ INSERT statement (after inserting the record, select the identity ID) --------->        Select @TransactionID = @@Identity    RETURN------------------------------------------------------...Dim transactionID As Integerconnection.Open()Dim reader As SqlDataReaderreader = command.ExecuteReader(CommandBehavio...

convert array values to integer
Hello, How do i test to see if the values in the arraylist are all integers, 2. How do i get the value back from the return value  ? Here is my code below thanks Ehi  protected void Button1_Click(object sender, EventArgs e) {string test = TextBox1.Text.ToString(); string[] ehi = test.Split(',');int i = 0; int e = ehi.GetUpperBound(0); do { try {int.Parse(ehi[i]); //return true; } catch { //return false; }     Response.Write(ehi[i].ToString()); i++; }  while (i <= e); } Hello,ArrayList stores the data as objects so you w...

How to convert Integer variable to Character
Hi, I have a value in an Integer variable , and I wanna convert this value to character. How can I do that. Appreciated You can type cast it... yourval.ToString() Are you looking for thisSushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb Like this: Dim I As Integer = 5 Dim S as string S = I.ToString() You might wanna add a try catch block to this....while (!asleep()) ++sheep;...

Convert to character from ASCII values in Dropdownlist
Hi,I want to bind data to asp dropdownlist.and data in tables contains following data ... aaaa&#153bbbb&#174cccc&#174 and so on.. my problem is when i bind it to dropdownlist i get item which contains ascii values...as it is.I wanted to convert them to respective keyword....but not able to find any lead...So suggest some links/ideals ....   RegardsAmit Dont know but this might help AscW(char)  ChrW(char) Eric  Hi Eric,I tried you suggestion but still not getting result.Let me know if have any other solution/suggestion.  RegardsAmit an...

Convert time values from string to integer
 Time stored into database like hh:mm:ss as a string and I need to convert hours, minutes and seconds to integer in order to complete my own calculation at the below. Does any one can help me please.Thank you Dim timeDiff As String            Dim ehours As Integer            Dim eminutes As Integer            Dim eseconds As Integer            ehours = Integer.Parse...

How can I pass a null value for an integer (value type) parameter and then check if this integer parameter is null?
Normally in VB.Net if I pass an integer parameter as 'Nothing', then in the called method this parameter is automatically set to zero. I want it to remain null so I can use the following code as it seems more intuitive than checking for 0 value: if moduleId is nothing then    'Do some processing here end if   In C#, I guess we can declare the parameter as int? moduleId and it can then be checked for null value.sun21170 The int? in C# is an example of using nullable types, which are a feature new to .NET 2.0. The ? is just syntactic sugar in the C# language....

Converting Characters to Other Characters from a DB
I have a SQL Server Database containing FAQ questions and answers... I had no problem getting a page built to pull the info and write it to a page... What I am having trouble figuring out is how to pull the info from the SQL DB and changing all of the newline ("\n") characters to HTML break tags ("<br />") so my output doesn't appear as one big paragraph... I have databind (or is it databound? Or databinded?) to a repeater and cannot for the life of me figure out how to work with the DataBinder.Eval(Container.DataItem,"ANSWER") to convert the ch...

converting web control string values to integers
I have a form using dropdown lists with numbers (1,2,3,4,5) I want to use these numbers to perform calculations but they are returned as stings. How can I convert them into integers? [C#] string MyString = "123"; int x = Int32.Parse(MyString); // throws an exception if MyString does not contain a numeric value such as "123b" [VB.NET] Dim MyString As String = "123" Dim x As Integer = Int32.Parse(MyString) ' throws an exception if MyString does not contain a numeric value such as "123b" Picky thanks much - that worked - was struggling...

Unicode Characters to Character Value
For an engraving machine I need to store/retrieve machining details for characters ('A' through 'Z', 'a' to 'z', '0' to '9' and characters like '&'). All characters are available in CodePage 1200. I would like to name my files according to the code point number (eg. 0041 = 'A'). How do I transform for example the UnicodeString 'A' to codepoint number 0041? This transformation would enable me to save the 'A' in Helvetica in file 'Helv_0041.fnt'. Jaap Schokker wrote: > For an engraving machine I ...

Convert integer to string value on dynamic gridview
I created a gridivew declaratively with linq to sql and it displays my data. However, I want the AccountType and AccountStatus fields to display in a string value. For example AccountType 1 = "Distributor", AccountType 2 = "Manufacturer" but in my database it of course are already set to 1 or 2. I do not want to change the database. <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSource...

Converting from value to another value
I had a value displayed as a total like -76.5 the user wants to see ($76.50).....so I did that by converting it to myvar.ToString("c")  which worked great.  Now when I run my stored procedure the "input is not in correct format" for obvious reasons.  How can I take the value ($76.50) and convert it smartly back to -76.5 for insertion?   please help.  very curious to see how this works because I tried to conver to string and no likey likey - my curiousity comes in where it knows that the (  ) means negative.....?     ok, bet...

how to convert string "9999999999" into integer value
Hi I have a string,then max value is "9999999999",10 characters how can i  convert it to integer,and the integer value's type is int32 or int64? I need help Thanks a lot     Your number is too large for an Int32, but you can parse your string into an Int64 like so: Int64 num = Int64.Parse("9999999999");Thanks, EdMicrosoft MVP - ASP/ASP.NET A normal integer(16 bits) cannot hold such value. You should use either long or Int64. It goes like this: Int64 tmpInt1 = Convert.ToInt64("9999999999"); or long tmpInt1 = Convert.ToIn...

Web resources about - Spliting a character value and converting it to integer - sybase.ase.general

Facebook Begins Converting Users To HTTPS
Are you willing to sacrifice a little bit of speed for a lot more safety? Facebook is asking that very question with its announcement that it ...

Facebook No Longer Converting Groups Into Pages
Back when Facebook first launched Facebook Pages, many businesses and brands who had built up substantial audiences in their Facebook Groups ...

Vert - simply converting for iPhone, iPad, and iPod touch on the iTunes App Store
Get Vert - simply converting on the App Store. See screenshots and ratings, and read customer reviews.

Converting SIM Card to Micro SIM Card - Flickr - Photo Sharing!
Place new Micro SIM into the iPhone SIM card tray

Ayaan Hirsi Ali on Converting Muslims to Christianity - YouTube
Complete video at: http://fora.tv/2010/07/29/Nomad_From_Islam_to_America_with_Ayaan_Hirsi_Ali Ayaan Hirsi Ali explains her support of missionary ...

Click go fears of converting print files
Is there a way to convert a print queue item to a .RTF or .PDF file? I like to save or email them. - The Sydney Morning Herald

Sudanese woman ordered to hang under sharia law for converting to Christianity gives birth
Khartoum, Sudan: A Christian Sudanese woman sentenced to hang for apostasy has given birth in jail, a Western diplomat said on Tuesday.

Imams warn against radicalism to Aboriginal inmates converting to Islam
The prison system has enlisted the help of ASIO to crack down on radicalisation behind bars amid revelations that Aboriginals are converting ...

Converting the world's companies one by one - The Science Show - ABC Radio National (Australian Broadcasting ...
Image: Trucks carrying logs make their way up a road in Jambi, Indonesia. A vast area of the Sumatran forest, and orangutan habitat, is being ...

Rothesay building new arena, converting existing rink to fieldhouse
The Town of Rothesay plans to build a new arena and convert the existing one into a fieldhouse.

Resources last updated: 1/5/2016 5:20:57 PM