Convert nvarchar to int

I have a sql table with field1 as nvarchar and field2 as int.  The values in the field1 have commas, negative numbers, null, blanks etc.  The below command handles the replacement of the "," (comma) but when I have a number that does not contain comma, I want to use that number in field2 and also when I have a number (12345), it does not convert to -12345 in field2.  I am not an expert at coding in sql....

UPDATE tblpoollist set field2= REPLACE(field1,',','') where field1 like '%,%'
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Jesus RodriguezIT ManagerCommented:
"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
Scott PletcherSenior DBACommented:
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.
MTMondayAuthor Commented:
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).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MTMondayAuthor Commented:
Why the 3 replace and where are u checking to see if it is numeric??
Scott PletcherSenior DBACommented:
The replaces are to get rid of:
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?

  37 --spacespace37
37  --37spacespace
  37  --spacespace37spacespace
3 7 --3space7

UPDATE tblpoollist
SET field2= REPLACE(REPLACE(REPLACE(REPLACE(field1,',',''), '(', ''), ')', ''), '-', '')
FROM tblpoollist
    SELECT CASE WHEN field1 LIKE '%,%,%' THEN 1
        WHEN field1 LIKE '(%)'
) AS assign_alias_names1
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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]%'

Open in new window

MTMondayAuthor Commented:
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.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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?
MTMondayAuthor Commented:
For the bad fields, field2 will be left blank.  I would like to replace the () with a '-' (negative sign in front of the number).
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Try this:
update t
set field2 = case when isnumeric(newfield2) = 1 then newfield2 else field2 end
select *, replace( replace( replace( replace( field1, ')', ''), '(', '-'), ',', ''), '$', '') as newfield2 from tblpoollist  
where field1 like '[-$(0-9]%'
) t 

Open in new window

It leaves field2 alone if it is not numeric after performing the requested transformations.

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
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
Microsoft SQL Server

From novice to tech pro — start learning today.