Copying integer values only -- sql server new users

sql server new users : Copying integer values only

HBOY
7/25/2005 12:00:00 AM

"eagle" <eagle@yahoo.com> ha scritto nel messaggio
news:OaxWxQTkFHA.2920@TK2MSFTNGP14.phx.gbl...
[quoted text, click to view]

If I suppose your table name is "yourTable" and your column name is
"strData" I suggest to use this simple query to create a new colum called
intData:

ALTER TABLE yourTable ADD COLUMN intData int;
UPDATE yourTable SET intData = CONVERT (int, strData) WHERE
isNumeric(strData) = true;

I can't try it now... let me know if it's ok :)
Bye

eagle
7/25/2005 10:16:18 AM

How can I copy data from a varchar field into an integer field, obviously
copying only the values that can be an integer?

For instance, I have data in a field like this:

1
2
A1034
432
B876

and I want to create a new field and copy over only those values that can be
numeric, i.e., 1, 2, 432, leaving the new field null if the original value
is not numeric

Hugo Kornelis
7/25/2005 10:54:46 PM

[quoted text, click to view]

Hi eagle,

IsNumeric (as suggested by HBOY) won't work - this will determine if the
value can be converted to any numeric type. Values that pass the
IsNumeric test might still fail to be converted to integer.

Assuming you only need positive integers, why not use

UPDATE MyTable
SET NewColumn = CAST(OldColumn AS int)
WHERE OldColumn NOT LIKE '%[^0-9]%'
(untested)

Best, Hugo
--

eagle
7/26/2005 12:53:38 PM

Thanks, all, in my case they both worked, although I could not do
isnumeric(x) = true, it errored saying there was no column named true, so I
changed it to isnumeric <> 0.

But since I have all integers, both ideas worked well. Thanks so much!

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn