Link to home
Start Free TrialLog in
Avatar of Chris Michalczuk
Chris MichalczukFlag for United Kingdom of Great Britain and Northern Ireland

asked on

trying to convert a str into a number in sql 2012

Select * from AUDIT_HEADER
where type  like ('SC%') and INV_REF_NUMERIC is NULL
go

update AUDIT_HEADER
set INV_REF_NUMERIC = (select CASE WHEN ISNUMERIC('Inv_ref') = 1 THEN CAST('Inv_ref' AS INT) ELSE NULL END)
go

how do I get any number only that exists in INV_REF (varchar (30) and put it into INV_REF_NUMERIC (int)

this doesnt seem to work?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

this should do:
update AUDIT_HEADER
set INV_REF_NUMERIC = CAST(Inv_ref AS INT) 
WHERE ISNUMERIC(Inv_ref) = 1

Open in new window

When you put quotes around the column name, it becomes a literal and no longer a column name.

For example, if you did this:
SELECT 'Inv_ref' FROM AUDIT_HEADER
the output would be "inv_ref" over and over, and not the value in that column.

Therefore, you want to do something like this:

update AUDIT_HEADER
set INV_REF_NUMERIC = CAST(Inv_ref as int)
WHERE inv_ref NOT LIKE '%[^0-9]%' --only digits 0 thru 9 (integer value); add '-' if the value can be neg.
Avatar of Chris Michalczuk

ASKER

update SLXAddressesMasterCleaned
  set zip2 =  CAST(Postalcode as int)
WHERE Postalcode NOT LIKE '%[^0-9]%' --only digits 0 thru 9 (integer value);

Postalcode     ZIP2
16280             16280
C1022AAR       returned nothing  I wanted it to return 1022

basically I want to return any numbers that exist in postalcode into zip2 only
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg 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