Ok my last formatting question.
How can I insert a money value as a padded string in another table?
example $1.25 gets inserted to another table as 00000125
I want 8 total characters and no decimal
another example would be 4,225.99 becomes 00422599
can this be done?
thank you!! 7 7442
declare @m money
set @m = $1.25
select @m, RIGHT(REPLICATE('0',8) +
convert(varchar(8),convert(int,@m*100)),8)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 10:15:08 -0800, pa********@gmail.com wrote:
>Ok my last formatting question.
How can I insert a money value as a padded string in another table?
example $1.25 gets inserted to another table as 00000125
I want 8 total characters and no decimal
another example would be 4,225.99 becomes 00422599
can this be done?
thank you!!
On Feb 22, 11:33 am, Roy Harvey <roy_har...@snet.netwrote:
declare @m money
set @m = $1.25
select @m, RIGHT(REPLICATE('0',8) +
convert(varchar(8),convert(int,@m*100)),8)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 10:15:08 -0800, paulmac...@gmail.com wrote:
Ok my last formatting question.
How can I insert a money value as a padded string in another table?
example $1.25 gets inserted to another table as 00000125
I want 8 total characters and no decimal
another example would be 4,225.99 becomes 00422599
can this be done?
thank you!!
I had a similar project. I did this and it works great:
REPLACE(REPLACE(CONVERT(char(8), @m), '.', ''), ' ', '0')
-Utah
thanks that worked great.
any idea why this doesn't work:
REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
it just won't seem to put the zero in....very strange
i get this for 15: '15 '
(pa********@gmail.com) writes:
thanks that worked great.
any idea why this doesn't work:
REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)),' ','0')
it just won't seem to put the zero in....very strange
i get this for 15: '15 '
Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.
It looks like a bug to me.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
i got it to work using:
RIGHT('0000'+REPLACE(SUM(tblLines.fldUnits), ' ', '0'), 4)
results in 0015 where SUM(tblLines.fldUnits)=15
Erland Sommarskog (es****@sommarskog.se) writes:
(pa********@gmail.com) writes:
>thanks that worked great.
any idea why this doesn't work:
REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)) ,' ','0')
it just won't seem to put the zero in....very strange
i get this for 15: '15 '
Good question. Seems like the reailing spaces are stripped when the
string is passed to replace(). Probably, because there is a conversion
to varchar, but trailing spaces should be retained, as long as the
setting ANSI_PADDING is in effect.
It looks like a bug to me.
For what it's worth, I submitted https://connect.microsoft.com/SQLSer...dbackID=259840
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland,
I've submitted a workaround to this Feedback item. If you
concatenate the empty string '' to the CHAR value before
REPLACE is applied, the hidden conversion to VARCHAR
retains the trailing blanks.
declare @t char(6)
set @t = 'A'
select
replace(@t,space(1),'*'),
replace(@t+'',space(1),'*')
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- C70DF007-1034-489C-A71E-108FBC89D553
Erland Sommarskog wrote:
Erland Sommarskog (es****@sommarskog.se) writes:
>(pa********@gmail.com) writes:
>>>thanks that worked great.
any idea why this doesn't work:
REPLACE(CONVERT(CHAR(4),SUM(tblLines.fldUnits)) ,' ','0')
it just won't seem to put the zero in....very strange
i get this for 15: '15 '
Good question. Seems like the reailing spaces are stripped when the string is passed to replace(). Probably, because there is a conversion to varchar, but trailing spaces should be retained, as long as the setting ANSI_PADDING is in effect.
It looks like a bug to me.
For what it's worth, I submitted https://connect.microsoft.com/SQLSer...dbackID=259840
But since it works this way in SQL 2000, I would not really expect
any fix. It could break existing code.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jim |
last post by:
im using asp.net, C# to enter data into a table in sql
server...however im getting this error:
Input string was not in a correct format.
Description: An unhandled exception occurred during the...
|
by: rong.guo |
last post by:
I would need to create multiple variables with the same format for
future update, what I did is listing them separately, is there an easy
way to combine them in one step? please see query below....
|
by: Niyazi |
last post by:
Hi
I am trying to insert some value into SQL Server 2000 tables and I am keep
getting the "Input string was not in a correct format" error.
When user fills the Form it updates the table call...
|
by: MM |
last post by:
Hi,
I have a param class containg these vars:-
string key; // eg: "WN"
object value; // eg: 1.2
string format; // eg "F2"
and I output these to a StreamWriter using
...
|
by: tshad |
last post by:
I can't seem to get the money format (String.Format("{0:c}") to work for
data coming from my database.
If I do
WagesMin.Text = String.Format("{0:c}",10000))
This will put "$10,000.00" into...
|
by: christian.bongiorno |
last post by:
I need to format a string and I would like to do it in 1 line
char buff;
I need something LIKE:
sprintf(buff,"",value); // space pad up to 32 chars
with the caveat it CANNOT be null...
|
by: Jameson |
last post by:
Hi,
Dumb question for you:
Is there a quick and easy way to ensure a string has a minimum width using
format specifier?
Should I just write
if myString.Length < 8 then myString.PadLeft (...
|
by: joemacbusiness |
last post by:
Hi All,
How do I format printed data in python?
I could not find this in the Python Reference Manual:
http://docs.python.org/ref/print.html
Nor could I find it in Matloff's great tutorial:...
|
by: Rowan |
last post by:
Hi,
I am somewhat new to .net and c#. (What I learned in previous co has to
be unlearned). I am doing something that seems simple but I think there
is a better way than how I learned to do it. ...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |