Convert numeric to varchar

Hi,

does anybody know how I can convert a number to a character datatype so 
that leading zero's will be in the result. E.g.:

56 -> '0056'

TIA.

Regards,
Jan
0
Jan_Sch
3/10/2003 2:55:18 PM
sybase.ase.general 8655 articles. 0 followers. Follow

5 Replies
409 Views

Similar Articles

[PageSpeed] 52

Hello,

I believe that the easiest way is to just add zeros to the string.

Example)
SELECT "0000000000" + CONVERT(VARCHAR(10), @VALUE)

You could also you replicate, stuff and other various system functions to 
perform this, but I believe the above is the easiest.
0
putnamr
3/10/2003 3:06:38 PM
/* Good for positive numbers only. */

1> select field1, "Positives Only" =
2>        right( replicate("0", 12) + convert(varchar, field1), 12)
3> from tempdb..Numbers  where field1 > -1
4> go
 field1      Positives Only 
 ----------- -------------- 
        1000 000000001000 
           0 000000000000 

(2 rows affected)

/* Good for positive and negative numbers. */


1> select field1, "Both Signs" =
2>        substring( "- +", (sign(field1) + 2), 1) +
3>        right( replicate("0", 12) + convert(varchar, abs(field1)), 12)
4> from tempdb..Numbers
5> go
 field1      Both Signs 
 ----------- ------------- 
        1000 +000000001000 
       -1000 -000000001000 
           0  000000000000 

(3 rows affected)

The position of the sign may be adjusted.

1> select field1, "Both Signs" =
2>        right( replicate("0", 12) + convert(varchar, abs(field1)), 12)
3>        + substring( "- +", (sign(field1) + 2), 1) 
4> from tempdb..Numbers
5> go
 field1      Both Signs 
 ----------- ------------- 
        1000 000000001000+ 
       -1000 000000001000- 
           0 000000000000 

(3 rows affected)

Data containing decimal values may also be shown as fixed strings.

1> select field1, "Both Signs" =
2>         substring( "- +", (sign(field1) + 2), 1) +
3>         right( replicate("0", 12) + ltrim(str( abs(field1), 9,2)), 12)
4>  from Number
5> go
 field1               Both Signs 
 -------------------- ------------- 
          1000.200000 +000001000.20 
         -1000.200000 -000001000.20 
             0.000000  000000000.00 

(3 rows affected)

Note: The use of str will round the right most column.
0
wkraatz
3/10/2003 3:12:30 PM
Hello,

How about just using a case statement?

SELECT 
  CASE 
    WHEN @VALUE >= 0
     THEN "+" + "000000000" + CONVERT(VARCHAR(9), ABS(@VALUE))
    ELSE  "-" + "000000000" + CONVERT(VARCHAR(9), ABS(@VALUE))
  END

OR

SELECT 
  CASE 
    WHEN @VALUE >= 0
     THEN "000000000" + CONVERT(VARCHAR(9), ABS(@VALUE)) + "+"
    ELSE  "000000000" + CONVERT(VARCHAR(9), ABS(@VALUE)) + "-"
  END

You could use the function replicate instead of hardcoding in the number of 
zeroes you need.
0
putnamr
3/10/2003 4:01:52 PM
Hi everybody,

thanks for all answers.

Regards,
Jan
0
Jan_Sch
3/10/2003 4:33:53 PM

Maybe this is what you're looking for...

select reverse( convert( varchar(4), reverse( convert( varchar(4), @value) ) + "0000" ) )

It could be adapted for any # of digits while yielding consistent length output strings.


Jan_Sch. wrote:
> 
> Hi everybody,
> 
> thanks for all answers.
> 
> Regards,
> Jan
0
Wally
3/13/2003 8:50:47 AM
Reply:

Similar Artilces:

Using CONVERT function to convert NUMERIC to VARCHAR
I am creating a trigger on a table where 2 columns are defined as numeric. This trigger captures the data and inserts it into another table where I want to combine the numeric data with a statement. I've tried using the CONVERT function, but it is giving me a mis-matched token error. I've looked through the documentation online, but it is not really helping me or I'm not understanding correctly. I've pasted a snippet of what I'm trying to do without using the CONVERT function. **user_level and region_id are the numeric values** select @whatchanged = case ...

error converting varchar to numeric
i have a huge stored procedure abt 500 lines..and i am calling this sp from an asp.net page...thn i got this error - error converting varchar to numeric - and am trying to debug...is there any way we can find out where the error is coming from...like aproxly which line number..etcor do i have to go through each line manually and see where i am doing the conversion.... thanks***********************Dinakar NethiLife is short. Enjoy it.*********************** Never tried it myself but it should let you step through a sproc like you would your C#/VB.Net code behind. Walkthrough: Debuggin...

how can i convert varchar to numeric
i am sending string like(1,2,3) by VB and want this string to used with In operator.but my problem is this that when i am using this in my sql select statement error occurs b'coz the table field by which i am quering is numeric. -- Posted via http://dbforums.com On 13 Oct 2003 14:37:14 -0700, in sybase.public.ase.general pragya <member41060@dbforums.com> wrote: >i am sending string like(1,2,3) by VB and want this string to used with >In operator.but my problem is this that when i am using this in my sql >select statement error occurs b'coz the table f...

how i convert varchar sal field to numeric in query
how i convert varchar sal field to numeric in query select sum(sal) from emp1 error:the sum or average aggregate operation cannot take a varchar data type as an argument. Use Cast as in: select sum(cast(sal as float)) from emp1...

Error converting data type varchar to numeric.
following is the error I am getting. Exception Details: System.Data.SqlClient.SqlException: Error converting data type varchar to numeric.Source Error: Line 107: " select '" & eventnumber & "','" & username & "','" & requestor & "', '" & bcode & "', max([Loan_ID]) from loan "Line 108: Dim myCommand1 As New SqlCommand(sql1, myConnection)Line 109: myCommand1.ExecuteNonQuery()Line 110:Line 111: Dim sql2 As String = " update Equipment set [Available_for_Loan] = 0 where barcode = '" &am...

Error converting data type varchar to numeric
hi All, This case statement is giving me the error "Error converting data type varchar to numeric". the column dbo.tblKMTermloanFees.base_min datatype is varchar.  And please see below for sample data in the column. i have to convert this into number. Any suggestions guys how to do this, Thanks in advance. Shilpa.  CASE  WHEN dbo.tblKMTermloanFees.base_min LIKE '%/6M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 6 WHEN dbo.tblKMTermloanFees.base_min LIKE '%/M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Mi...

Arithmetic overflow error converting varchar to data type numeric
Hello, i am getting "Arithmetic overflow error converting varchar to data type numeric"  while i am trying to cast the numeric value to varchar in SQL Server 2000(This is the Query  (cast(' + @Qty + ' as varchar(50)) If any knows plz help me, thanks.        "Arithmetic overflow error converting varchar to data type numeric" stands for error while converting varchar to numeric, and you say "trying to cast the numeric value to varchar", this is kind a confusing thing. Can you explain your problem more detailed?Nev...

How to convert numeric primary key into identity? ASE 12.5.2
How to convert numeric(19,0) primary key into identity? I have some tables with data where primary key is numeric(19.0). I need to convert such id into identity to allow auto increment. As I understand new next value problem must be solved somehow too. Any help? Using ASE 12.5.2 > How to convert numeric(19,0) primary key into identity? > I have some tables with data where primary key is > numeric(19.0). I need to convert such id into identity to > allow auto increment. As I understand new next value > problem must be solved somehow too. Any help? > Using ASE 12.5....

PB 11.2/MS SQL 2005 Error converting data type varchar to numeric
Am getting the above error from PB 11.2 when executing a stored proc. The proc take no parameters. Once i get this error, i cannot execute the proc succesfully from PB 11.2 until i recompile the stored proc. At the same time the proc works fine from Query Analyzer, as well as from PB 7. What is PB 11.2 doing to mess this up? Any ideas....it's wrecking havoc on our production app. Forgot to add the error is C0195:SQLSTATE =3D 22018 On Dec 13, 4:00=A0pm, bre...@friesens.com wrote: > Am getting the above error from PB 11.2 when executing a stored proc. > The proc take no...

ASE and ASE
Could it be possible to take scripts from ASA and load them in an ASE Any information is welcome jean-fran�ois ASA supports a fairly large sub-set of Transact-SQL, so if you write your stored procedures and triggers in ASA using T-SQL, you should be able to create scripts that will run against both ASE and ASA. If you're planning to do this though, I would suggest developing your database schema against ASA, since everything you write in T-SQL in ASA will be supported on ASE, but the reverse is not true. Check out the section in the ASA documentation entitled "Tra...

What generals generally do
Reading a McChrystal thread elsewhere, I came across someone quoting a US general http://en.wikipedia.org/wiki/Smedley_Butler > I spent 33 years and four months in active military service and > during that period I spent most of my time as a high class thug for > Big Business, for Wall Street and the bankers. In short, I was a > racketeer, a gangster for capitalism. I helped make Mexico and > especially Tampico safe for American oil interests in 1914. I helped > make Haiti and Cuba a decent place for the National City Bank boys to > collect revenues in. I ...

ASE to ASE
Hi, Is anyone out there doing ASE to ASE replication using SQL remote? If you are have created your own version of the SSEXTRACT utility to set up a replicant database in ASE rather than ASA? Or does anyone know if SYBASE has created a version SSEXTRACT for use with ASE to ASE replication? Thanks in advance Doug Trainer Hi Douglas, I thought the SQL Remote Replication support in ASE11.5 was meant for a consolidate-database only (by design)! At least that was what Sybase said when they announced support for SQL Remote technology support in ASE11.5. I will be happy to...

Converting NT 11.5 ASE to a Solaris 12.5 ASE
We are trying to migrate a server running on Windows NT to a Solaris machine. The NT box is running ASE 11.5 and the Solaris box is running 12.5. So far, the only way we have came up with to migrate the data is bcp. The problem is we estimate this to be probably about a 6-7 hour process. Our shop runs 24*7*365 and unfortunately downtime is not something we get. In the past when we have migrated servers, we have used warm-standby which has worked really nicely. But in this case we can't run dump and load to populate the database. Has anyone been able to migrate an NT box to a...

Help needed: arithmetic overflow error converting numeric to data type numeric
Hi, I have a SQL server 2005 db. The table structure (from big list of columns, only decimal columns I mentioned) below.  CREATE TABLE [Product].[MetricMeasures](    [MetricMeasureID] [int] IDENTITY(1,1) NOT NULL,    [ProductID] [int] NOT NULL,    [ProductSizeValue] [decimal](4, 2) NULL,    [ItemWeightValue] [decimal](3, 3) NULL,    [MasterCartonWeightValue] [decimal](3, 3) NULL,    [MasterCartonVolumeValue] [decimal](4, 4) NULL, CONSTRAINT [PK_MetricMeasures] PRIMARY KEY CLUSTERED ( &nbs...

Web resources about - Convert numeric to varchar - sybase.ase.general

File:Convert to SVG and move to Commons.svg - Wikipedia, the free encyclopedia
As a courtesy (but not a requirement), please e-mail me or leave a note on my talk page if you use this image outside of Wikipedia. Thanks! As ...

Facebook Co-Founder Dustin Moskovitz Sells 450K Shares, Converts 7M More
The great Facebook stock sale frenzy continues on with a report that Co-Founder Dustin Moskovitz unloaded 450,000 shares in the social network ...

Facebook Announces “App2User” Liquidity Program for Merchants to Convert Rewards Points into Credits
... for Credits, just announced a new Credits program called “App2User,” designed to enable merchants and loyalty program operators to convert their ...

How to Convert Leads Into Buying Customers - The 9 Step Sales Process EVERYONE Should Use
... School of Business in Toronto on the topics of Sales and Entrepreneurship. I shared with them the sales process that I find How to Convert Leads ...

Bitcoin : Should I convert my savings to Bitcoin?
Answer (1 of 7): You should think of this question as "Should I invest all my savings in Bitcoin?", and not "Should I have my savings denominated ...

Convertizo 2 - Convert Units and Currency in Style on the App Store on iTunes
Get Convertizo 2 - Convert Units and Currency in Style on the App Store. See screenshots and ratings, and read customer reviews.

Opinion: These 5 things made me a happy iPad Pro convert
... like I needed a bigger device. But after three weeks with my iPad Pro, several things have changed my mind, and I’m officially a happy convert ...

$100 device converts all your film to digital photos
... still on film negatives. Today, we've got the perfect Amazon Cyber Week deal for you: A Wolverine F2D Mighty 20MP 7-in-1 Film to Digital Converter ...

WWE diva converts to Christianity, confesses to a life of sin
Stunning wrestling diva Taryn Terrell has shared a dramatic knock-down testimony of conversion from life in the ring to life with Christ.

Stanford Designs Underwater Solar Cells That Convert Greenhouse Gases Into Fuel
... solar industry and the battle against climate change. Importantly, writes Stanford [&hellip Stanford Designs Underwater Solar Cells That Convert ...

Resources last updated: 12/5/2015 7:09:46 PM