Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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?
0
Chris Michalczuk
Asked:
Chris Michalczuk
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
update AUDIT_HEADER
set INV_REF_NUMERIC = CAST(Inv_ref AS INT) 
WHERE ISNUMERIC(Inv_ref) = 1

Open in new window

0
 
Scott PletcherSenior DBACommented:
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.
0
 
Chris MichalczukConsultantAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is also possible, check this previous solved question to get a simple helper function to do that:
http://www.experts-exchange.com/Programming/Microsoft_Development/Q_27682377.html
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now