Link to home
Start Free TrialLog in
Avatar of MTMonday
MTMondayFlag for United States of America

asked on

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 '%,%'
Avatar of Jesus Rodriguez
Jesus Rodriguez
Flag of United States of America image

"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.
Avatar of MTMonday

ASKER

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]%'

Open 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).
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial