sql update move spaces to numeric column

I need to move spaces to a numeric field in a table. I am using the update statement. How would I do this?
ryannAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You cannot do this. A numeric value has no spaces.

You may format it in your final query by converting it to [N][VAR]CHAR(), but this kind of formatting is better done in the front-end.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us a data mockup of what you mean by 'move spaces to a numeric field'.

Chances are this will not be compatible with a Numeric (int, decimal, etc.) data type, and you'll either need to cast this as a varchar, or just handle it in the presentation layer.
0
ryannAuthor Commented:
This is the field I need to mve spaces to in the update:

        [InvoiceNumber] bigint,
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us a data mockup of what you mean by 'move spaces to a numeric field'.

Something like...

Current:  14.56
I need to change this to:  {3 spaces}14.56{2 spaces}
0
ryannAuthor Commented:
[InvoiceNumber] bigint,

Current:  4256
I need to change to 2 spaces

thanks.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So, 4256 becomes two spaces?

As we've stated, you can't include spaces in any numeric data type, including bigint.
If this is for a feed, I recommend using a view/SP/whatever that does something like this:
   
SELECT CASE WHEN InvoiceNumber=4256 THEN '' ELSE CAST(InvoiceNumber as varchar(50) END

Open in new window

If this is for a report I recommend handling it in the report itself.

SInce there will always be sort/add operations on invoice numbers, I HIGHLY recommend not converting the data type itself to a varchar.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw, please check out my article on Top 10 Ways to Ask Better Questions, especially Number Ten..  State your requirements upfront...
0
sameer2010Commented:
Another option would be to have computed field on the table with such processing
0
PortletPaulfreelancerCommented:
"Move spaces" Means nothing to me, it might mean something to you, but I suspect most are like me.

What does "move spaces" mean?

Perhaps an example would help?
0
ryannAuthor Commented:
I am creating a csv file from a stored proc. If the rectype  =  payment the customer wants spaces in the InvoiceNumber column.

What I want to do is update the table to move spaces to the InvoiceNumber  column when it is a payment record.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, you really need to do a better job stating requirements upfront.  As Paul pointed out, 'Move spaces' can have multiple meanings, and it's not an efficient use of expert time for us to guess when you can spell it out for us, which means some experts may not answer your questions.  

>I am creating a csv file from a stored proc. If the rectype  =  payment the customer wants spaces in the InvoiceNumber column.
Well ok then.  Couple of options:
You can also use SPACE(#) to add a defined # of spaces. SELECT 'Hi' + SPACE(3) + 'there!'
If we're talking a defined width where you need to left-align a number of unknown length with spaces, try to left pad spaces and take the right # of characters.  For example, to take the value '4567' (could be any length) and left-space-pad it into eight characters... SELECT RIGHT(SPACE(8) + '4567', 8)
0
PortletPaulfreelancerCommented:
You want to "substitue" a space IF a value is null

The column in question is numeric and numeric columns cannot have a space, so it has to be converted to string too.

E.g.

Select ISNull(cast([numeric-column] varchar(12)), '' ')
.

You need to convert the data to nvarchar or varchar before that column can contain a zlace
0
sameer2010Commented:
On a side note, MOVE SPACES TO is so very like MAINFRAME COBOL programming and it does support such movement too ;)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ryannAuthor Commented:
What I am trying to say is that If the record in the table is a payment type I do not want to show the InvoiceNumber value. It should be blank .   Sorry for any confusion on my part explaining this.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>If the rectype  =  payment the customer wants spaces in the InvoiceNumber column.
>If the record in the table is a payment type I do not want to show the InvoiceNumber value.

Something like..
SELECT CASE WHEN rectype  =  payment THEN NULL ELSE InvoiceNumber END as whatever

Open in new window

>I need to move spaces to a numeric field in a table.
>This is the field I need to mve spaces to in the update:   [InvoiceNumber] bigint,
>Current:  4256     I need to change to 2 spaces
>If the rectype  =  payment the customer wants spaces in the InvoiceNumber column.
>If the record in the table is a payment type I do not want to show the InvoiceNumber value.

Your stated requirements are all over the board here, so I propose that I'll stop commenting to allow you to step back, gather your thoughts, and post complete requirements into this question.  Then I'll comment again.
0
sameer2010Commented:
Then it should be
SELECT CASE WHEN PAYMENT_TYPE = 'PAYMENT' THEN '' ELSE INVOICENUMBER END INVOICENUMBER FROM YOUR_TABLE

Open in new window

0
ste5anSenior DeveloperCommented:
Just a comment:

A CSV row like "value1,value2,  , value4" means that in column three has a value of "  ", which is a string and not a number. So, under certain circumstances, this will get ugly for the consumer of the CSV.
0
ryannAuthor Commented:
I am withdrawing this question. I thought I could use cast or convert. I don't have much experience yet. Sorry for the confusion.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yeah, about that .. I'm not going to allow this question to be deleted without closure, as too many experts made a good-faith attempt to help you, so a delete would be a dis-service to their efforts.   So you're going to have to make an effort to firm up your requirements, then ask them, then get a solution.
0
ryannAuthor Commented:
Sameer got where my confusion was on this question.  Most of my past experience is with COBOL. I did not know that asking the question like this for sql would cause such confusion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.