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 '%,%'
MTMondayAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Try this:
update t
set field2 = case when isnumeric(newfield2) = 1 then newfield2 else field2 end
from 
(
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.
0
 
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
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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).
0
 
MTMondayAuthor Commented:
Why the 3 replace and where are u checking to see if it is numeric??
0
 
Scott PletcherSenior DBACommented:
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
0
 
QlemoBatchelor, 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

0
 
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.
0
 
QlemoBatchelor, 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?
0
 
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).
0
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.

All Courses

From novice to tech pro — start learning today.