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

x
?
Solved

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

Posted on 2013-11-07
3
Medium Priority
?
346 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

916 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