Solved

Convert a text field to an Integer and splitting a field to the Right

Posted on 2013-11-07
3
318 Views
Last Modified: 2013-11-07
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_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,

FROM         dbo.vw_PayCurrentMths

ORDER BY EmployeeNo
0
Comment
Question by:CMChalcraft
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
guswebb earned 500 total points
ID: 39630105
Remove the hash = RIGHT(EmployeeNo,(LEN(EmployeeNo)-1))

Convert to integer = CONVERT(Int,EmployeeNo)
0
 
LVL 9

Expert Comment

by:guswebb
ID: 39630117
Get first name (or string pre the comma) = SELECT LEFT(Name, (CHARINDEX(', ',Name,0)-1))
0
 

Author Closing Comment

by:CMChalcraft
ID: 39630674
Very helpful. Thanks.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Disable Server 2012 folder redirection 2 58
Access DB on open (All Objects) 5 68
iPhone excel activation issues 11 119
SQL 2016 licensing 6 65
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question