sql update move spaces to numeric column

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.

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.

This is the field I need to mve spaces to in the update:

        [InvoiceNumber] bigint,

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}

[InvoiceNumber] bigint,

Current:  4256
I need to change to 2 spaces

thanks.

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

Select allOpen 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.

btw, please check out my article on Top 10 Ways to Ask Better Questions, especially Number Ten..  State your requirements upfront...

Another option would be to have computed field on the table with such processing

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

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.

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

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.

>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

Select allOpen 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.

Then it should be

SELECT CASE WHEN PAYMENT_TYPE = 'PAYMENT' THEN '' ELSE INVOICENUMBER END INVOICENUMBER FROM YOUR_TABLE

Select allOpen in new window

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.

I am withdrawing this question. I thought I could use cast or convert. I don't have much experience yet. Sorry for the confusion.

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.

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.