Convert a text field to an Integer and splitting a field to the Right
Posted on 2013-11-07
I am try to convert a text field which contains the employee number to an integer. The field is in the format #87, #123, #2. I want to remove the hash and convert to an Integer.
I have been using CAST(SUBSTRING(Number, 2, 4) AS INT) AS EmployeeNo. This appears to work. However when I try later to use this field in an INSERT statement I get the following message :
"Conversion failed when converting the varchar value 'dbo.vw_PayCurrentEmployees02.EmployeeNo' to data type int."
So it is not converting to an Integer?
I also have another field that has the whole name, "Smith, John", Brown, Gordon". How do I select the First Name i.e "John" or "Brown"?
The query I am using is :
SELECT TOP (100) PERCENT Number, Name, CAST(SUBSTRING(Number, 2, 4) AS INT) AS EmployeeNo, CASE WHEN Name LIKE '%,%' THEN RIGHT(Name, charindex(', ', Name) + 13) ELSE Name END AS FirstName,
ORDER BY EmployeeNo