Chris Michalczuk
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?
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?
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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window