Convert nvarchar to int

"I have a number (12345), it does not convert to -12345 in field2"

If field 2 is and integer it won't display - on negative numbers. Integers goes from 0 to a range of numbers that now don't remember.  If you need to do that you need to change the type of the Field 2 to Money or Float and then you can use your expression

UPDATE tblpoollist
SET field2= REPLACE(REPLACE(REPLACE(REPLACE(field1,',',''), '(', ''), ')', ''), '-', '')

Btw, int values in SQL Server can contain negative values, and if the character input contained a minus sign, SQL would convert it to a negative value.

Thanks for the reminder....what about if the number does not have the comma, how would I set my query for the logic:
    1).  first check to see if it isnumeric,
    2).  if it is then check for comma, if there are commas, replace them (which I am doing)
   3).  But if it isnumeric and does not have the comma, I still want the field2 to be updated (Where I am stuck).

Why the 3 replace and where are u checking to see if it is numeric??

The replaces are to get rid of:
comma
left paren
right paren

But, if you want to insure the data is a valid number, I need to adjust it.

What specific formats of data do you want to allow?  Would each value below be good/accepted or bad/not?

7.
(7)
7,
7,7
7,7,7
-7
-7,1
-7-1
  37 --spacespace37
37  --37spacespace
  37  --spacespace37spacespace
3 7 --3space7


UPDATE tblpoollist
SET field2= REPLACE(REPLACE(REPLACE(REPLACE(field1,',',''), '(', ''), ')', ''), '-', '')
FROM tblpoollist
CROSS APPLY (
    SELECT CASE WHEN field1 LIKE '%,%,%' THEN 1
        WHEN field1 LIKE '(%)'
) AS assign_alias_names1

Am I correct that commas represent thousands etc., and numbers in parens are negative, and should stay negative (but with a minus sign)? This is a simplified command, which should be sufficient but not fail-safe.

update tblpoollist set field2 = replace( replace( replace( field1, ')', ''), '(', '-'), ',', '')
where field1 like '[-(0-9]%'

Select allOpen in new window

Scott:  I would like to allow the following formats:
   1).  (123ab123) -- Bad
   2).  123ab123 -- Bad
   3).  (123,1234) -- Good
   4).  1233,456 --Good
   5).  $1234,567 -- Good
   6).  1234 -- Good (even though it does not have comma, set the field2 as 1234, because it is numeric)
   7).  (1234) -- Good (same logic as above).

I do not see where you are checking for () and if it is a valid number?  Thanks for your help.

You can only check after the replacement, because parens and commas are not part of valid numeric values.
What should happen with the "bad" ones?

For the bad fields, field2 will be left blank.  I would like to replace the () with a '-' (negative sign in front of the number).