Convert a text field to an Integer and splitting a field to the Right
Hi,
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_PayCurrentEmployee
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,
FROM dbo.vw_PayCurrentMths
ORDER BY EmployeeNo
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_PayCurrentEmployee
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,
FROM dbo.vw_PayCurrentMths
ORDER BY EmployeeNo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
guswebb
Get first name (or string pre the comma) = SELECT LEFT(Name, (CHARINDEX(', ',Name,0)-1))
ASKER
Very helpful. Thanks.