Solved

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

Posted on 2013-11-07
3
324 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to delete range 4 84
Edit header 19 139
COPY from excel to notepad 3 86
SCOM logon account permissions? 1 40
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

752 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