By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,648 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,648 IT Pros & Developers. It's quick & easy.

Convert Text to int but output as text

P: 1
I want add a number to a text value and have it appear in
output. Original value column Extension is a text that I
want to add 2100, but I want to have it output to a text.

Using Northwind database, table Employees:

Expand|Select|Wrap|Line Numbers
  1. Select 
  2.   (E.EmployeeID) as 'EmployeeID_ab',
  3.   (E.Extension)  as 'Extension_ab' 
  4. From Employees E
Returns rows like this:
Expand|Select|Wrap|Line Numbers
  1. EmployeeID_ab  Extension_ab
  2.      1           5467
  3.      2           3457
I want rows like this after adding 2100:
Expand|Select|Wrap|Line Numbers
  1. EmployeeID_ab  Extension_ab
  2.      1           7567
  3.      2           5557
Dec 14 '12 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,769
I doubt that's true. The syntax for the SQL appears wrong to me in that the ALIAS commands (abbreviated to as in your case) use string literals instead of valid names.

You're probably looking for :
Expand|Select|Wrap|Line Numbers
  1. SELECT CAST(E.EmployeeID AS int) + 2100 AS [EmployeeID_ab]
  2.      , CAST(E.Extension AS int) + 2100 AS [Extension_ab]
  3. FROM   Employees AS E
Dec 14 '12 #2

Rabbit
Expert Mod 10K+
P: 12,430
In SQL Server, it is valid to put the alias in quotes.

If you want the final result to be a text data type, take what NeoPa did and put another cast around the expression and cast it back to text.
Dec 14 '12 #3

NeoPa
Expert Mod 15k+
P: 31,769
Rabbit:
In SQL Server, it is valid to put the alias in quotes.
My bad. Rabbit is far more experienced than I am in SQL Server as I no longer have one to work on even, so if he says I'm wrong, then the chances are pretty high that I am.

He is also correct that I overlooked your clear request to convert it back to Text afterwards. I was only thinking of it displaying on the screen, but that wasn't the question. So :
Expand|Select|Wrap|Line Numbers
  1. SELECT CAST(CAST(E.EmployeeID AS int) + 2100, AS nvarchar) AS [EmployeeID_ab]
  2.      , CAST(CAST(E.Extension AS int) + 2100, AS nvarchar) AS [Extension_ab]
  3. FROM   Employees AS E
Dec 14 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.