Converting numeric value to string with leading zeroes

Hi,

We have a smallint column on a table in which we store ISO country numeric
codes. These values range in length from 1 to 3 (when stored as smallint).

However we now have a requirement that we output them to a flat file they
must be of length 3 with leading zeroes if required e.g. 76 must be '076'.

Obviously I could just do a few IF statements with DATALENGTH and STR  but I
was wondering if someone has a nice tidy way if doing it.

Eoin


0
Eoin
5/3/2000 9:36:29 AM
sybase.sqlserver.general 4269 articles. 0 followers. Follow

2 Replies
673 Views

Similar Articles

[PageSpeed] 49
Get it on Google Play
Get it on Apple App Store

> Obviously I could just do a few IF statements with DATALENGTH and STR  but I
> was wondering if someone has a nice tidy way if doing it.

select right('000'+convert(varchar(3),tinyint_column),3)
	from	table_name

Just doublecheck the syntax on the 'right()' function ... I'm doing this
from memory.

-- 
Mark A. Parsons

Iron Horse, Inc. 	iron_horse@compuserve.com
Pegasys (200), Ltd 	pegasys@internet.co.nz
0
Mark
5/3/2000 9:45:42 AM
Thanks Mark. It worked a treat.

Mark A. Parsons <pegasys@_internet.co.nz> wrote in message
news:390FF546.E42C06DC@_internet.co.nz...
> > Obviously I could just do a few IF statements with DATALENGTH and STR
but I
> > was wondering if someone has a nice tidy way if doing it.
>
> select right('000'+convert(varchar(3),tinyint_column),3)
> from table_name
>
> Just doublecheck the syntax on the 'right()' function ... I'm doing this
> from memory.
>
> --
> Mark A. Parsons
>
> Iron Horse, Inc. iron_horse@compuserve.com
> Pegasys (200), Ltd pegasys@internet.co.nz


0
Eoin
5/3/2000 10:13:58 AM
Reply:

Similar Artilces:

how to convert null to zero or other numeric value at data object level?
Can we convert null value into zero or other numeric values or even set its value to other column value at data window object level? (the column must be editable though -- not using computed column) I mean can we done it without having to code like this: lc_amt = dw_1.getitemdecimal(dw_1.getrow(), "amt") if isnull(lc_amt) then dw_1.setitem(dw_1.getrow(), "amt", 5) the conversion must take place at data object itself not after it is placed in a window. thanks, rony It would depend on the DBMS you are using. In Oracle you can use NVL in the SQL of your...

superreview requested: [Bug 245619] Don't bother converting result of scripts into a string when the caller doesn't care about the value. : [Attachment 150056] Don't convert the result to a string if
Johnny Stenback <jst@mozilla.jstenback.com> has asked Brendan Eich <brendan@mozilla.org> for superreview: Bug 245619: Don't bother converting result of scripts into a string when the caller doesn't care about the value. http://bugzilla.mozilla.org/show_bug.cgi?id=245619 Attachment 150056: Don't convert the result to a string if the caller doesn't care about the return value. http://bugzilla.mozilla.org/attachment.cgi?id=150056&action=edit ...

superreview granted: [Bug 245619] Don't bother converting result of scripts into a string when the caller doesn't care about the value. : [Attachment 150056] Don't convert the result to a string if t
Brendan Eich <brendan@mozilla.org> has granted Johnny Stenback <jst@mozilla.jstenback.com>'s request for superreview: Bug 245619: Don't bother converting result of scripts into a string when the caller doesn't care about the value. http://bugzilla.mozilla.org/show_bug.cgi?id=245619 Attachment 150056: Don't convert the result to a string if the caller doesn't care about the return value. http://bugzilla.mozilla.org/attachment.cgi?id=150056&action=edit ------- Additional Comments from Brendan Eich <brendan@mozilla.org> Duh, thanks. r+sr=me....

How to convert text string to numeric string? Pls help.
Dim strApprove As String = "" Dim selIndex As Integer For Each selIndex In rsc.SelectedIndexes strApprove = strApprove + CStr(dg.DataKeys(selIndex)) + "," Next strApprove = Mid(strApprove, 1, ((Len(strApprove) - 1)))  strApprove = 123,234,233,323 (these are id #s) I want to convert strApprove into numeric string(123,234,233) so I can use it in my SQL query.  The ID field is defined as decimal in the database table. I've tried Val() and CLng and didn't get it to work.  Thanks for your help. Dim intApprove as Integer = CType(strApprove, Integer)Community C...

Convert INT value to STRING so I can add it to another STRING
I'm trying to figure out how to convert an INT so that I can add it to another string in a statement likeSELECT '<a href="http://mydomain.com/' + [IntValue] + '">' + [LinkName]Where [IntValue] is an INT field containing a folder number.  I tried: TO_CHAR([IntValue, '999')but it didn't work. And I tried to find a TO_STRING function, but had no luck.Any advice to someone who is savy, but new to SQL Queries? SELECT '<a href="http://mydomain.com/' + STR([IntValue]) + '">' + [LinkName]HTHregardsASP.NET Hosting (HostingFest) Bingo!  I had to restrict the fu...

compilation error-- value of string cannot be converted to 1-dimensional array of string
For j = 0 To aroles.Items.Count - 1 If aroles.Items(j).Selected = True Then If Not Roles.IsUserInRole(username.ToString, aroles.SelectedValue) Then Roles.AddUsersToRole(username.ToString, aroles.SelectedValue) <-------  Error occurs here...- ' test3.Text = aroles.SelectedValue(j) End If End If Next Hey can anyone help me with this thanx heaps Mrugesh     Use the method: AddUserToRole instead of the one that adds "Users" to the role. The method that add "users" want an array of usernames, but you only provide a single string with one ...

Leading zero in a string
Hi All, I have a variable I'm reading off of the command line: my $option = shift; That variable should hold a number between 1 and 31 (yes, a day of the month) I am checking to make sure that the number does indeed lie in that range. However, I need to pass that variable to another system command which expects any "day" value less than 10 to have a leading zero, so 7th day of the month should say '07'. How can I check for that leading zero? If it's missing, I know I could easily: $option = "0".$option; But I can't seem to figur...

Converting a Numeric/String
OK, I know this is easy, but I am having problems with it.  I have a value in the database (e.g. "27372581") and I am trying to figure out how todisplay it as "27,372,581"How is this done?Karls------------------------------------'Old and wrinkled is not what I expected to become when I grew up!' I figured it out:The FormatNumber() function returns a value formatted as a number. Its general format is shown below.FormatNumber(value [, trailing digits] [, leading digit] [, parentheses] [, group digits]) value is any expression that produces a number;trailing digits is an intege...

Zero-width split() match creates empty trailing strings but not empty leading strings
The perlfunc documentation spells this out clearly, and it matches what I see: $ perl -e 'for (split(//, "fob", -1)) { print "$_\n"; }' | sed -e 's/^$/<blank>/' f o b <blank> The question on my mind is why. In particular, is it a decision worth replicating to language's libraries? Thanks for any pointers. I tried to find the source for split, and I think I may have found it in pp_split in pp.c. But there's not really any reason to expect the source code to include the justification, and I couldn't find one. http:/...

setsort for numeric order by with strings that contains numbers ( dw_1.SetSort("convert(numeric(5),ls_number)+ ",A")
Dear all, there is somebody that know how to get a Sort of dw on string field that contain surely a number ? ES. following construct is not good: dw_1.SetSort("convert(numeric(5),string_field)+ " ,A") there is some form to get the numeric sort order (not ascii) ? Thank you in advance Roby You need not convert string to number to do sort. sort will automatically does that for you in the order of first number sort then string sort. "Roberto Lammoglia" <NOSPAMrlamm@becromal.it> wrote in message news:3f65b184@forums-1-dub... > ...

validate string if all the values are zero.
Hi all, here is my string that return the value, dim strID as string="2343-dsds3-65gff" if the values in all the strID are zero,i.e.,strID as string="0000-00000-00000"i want to display error. Plese tell me how to do it. i'm using vb.net 2.0Thanks SwapnaPlease click "mark as answer" if this post helped you. Hi Swapna, What bout eriting a function like below, public bool validateString(string mystring) {             bool valid=true;          &...

Help me : Convert a string into a numeric
Hello, I have a string "0600" that i want to convert into a d�cimal 0.6 to write into a file .... I 'm trying to do : li_tmp=Integer(mystring) // with mystring ="0600" li_tmp=li_tmp/1000 But it's wrong Can Somebody Help me ! Thanks try using a decimal datatype instead of integer "Pascal Perrin" <pascal.perrin@prosimo.com> wrote in message news:t5fEJVk8$GA.203@forums.sybase.com... > Hello, > > > I have a string "0600" that i want to convert into a d�cimal 0.6 to write > in...

how to convert to string value then loop
 I want to convert my Paramter ID "o127625", to a sting value and thenhave it loop. It appears a link, but I want to convert it and then loop it for results for my reports . Please help me.  CODE:::::::::::::::::::::::   Public Overrides Sub Run()            Dim request1 As WebTestRequest = New WebTestRequest("http://template.qa.stratix.biz/Shared%20Documents/invoice.aspx")            dim            ...

How do I convert a string to a DateTime value??
Ok...I give up.... It use to be (in versions 1.0/1.1 of the framework) you could convert a string to a datetime value by simply executing the following:  System.DateTime X=System.Convert.ToDateTime(<string value>); as long as you used the System library. Unfortunately,  it would appear that Microsoft got smart and has left out this ability in the 2.0 framework in it's never ending attempt to screw the developer. Now it's quite true that I could write a parser to parse the string....but I would STILL need to convert the items to a number.  Any ideas?? PS:  I'...

Web resources about - Converting numeric value to string with leading zeroes - sybase.sqlserver.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 ...

Converting Churches Into Homes Is The Latest Hollywood Trend
You don't have to be a believer to be moved by the beauty of a church.

Resources last updated: 11/26/2015 7:51:08 AM