Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
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.
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…

722 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