Joining two cells togeter on tables in SQL 2005

Posted on 2010-08-30
Last Modified: 2012-05-10
Hello experts I have a fun project that I have inherated.... it involves a table that was built 3 years ago and the designer used CHAR and not VARCHAR.... here is my situation

Semester char(20)
SchoolYear int.

My current statement
select Semester + SchoolYear as SemesterSchool from Schedule

My current Error
Conversion failed when converting the varchar value 'F2UU                ' to data type int.

1. How do I corret the spacing trim().
2. All I need to do is join these two collums into one

1. Cant change the original tables in the database
2. Boss is on me... any help?
Question by:HawaiiDragon
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

Assisted Solution

birdmian earned 100 total points
ID: 33562750
Try like this:

SELECT rtrim(Semester) + ' ' + convert(char(4), SchoolYear)

Expert Comment

ID: 33562783
try the convert statment

select convert(varchar, Semester)  + rtrim(SchoolYear) as SemesterSchool from Schedule

LVL 16

Assisted Solution

vdr1620 earned 100 total points
ID: 33562788
SELECT rtrim(Semester) + ' ' + convert(Varchar(4), SchoolYear)
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Accepted Solution

joeyw earned 300 total points
ID: 33562791
oops had your fields backwards

try the convert statment

select rtrim(Semester)  + convert(varchar,SchoolYear) as SemesterSchool from Schedule

Expert Comment

ID: 33564442
Try the Following Code:

select ltrim(rtrim(semester))+ltrim(rtrim(convert(varchar,schoolyear) as 'Alias' from Schedule

let me know if you need any more help

Author Closing Comment

ID: 33567525
All solutions worked however Joeyw's worked the best as removing all trim form the fileds as well as joining them in the correct way. Thank you all!!!!!

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
denied execute as 13 57
Database Mail Profiles 1 51
SQL query 7 49
What does "Between" mean? 6 43
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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