concat three number columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

concat three number columns

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-02-25 : 09:17:55

In a table there are three number columns need to concat:

column1 = 123
column2 = 456
column3 = 789

I want to use a views to display like 123456789.

How to do it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-25 : 09:23:05

if the columns are string, just
select column1 + column2 + column3 as [column123]
from yourtable

----------------------------------
'KH'
It is inevitable

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-02-25 : 09:27:14

The problem is all three columns are smallint type. This for a telephone list table: [area code] = 212, [exchangeCode] = 304, [lastCode] = 7788, I want to concat them to be: 2123047788

joelpravin
Starting Member

3 Posts

Posted - 2006-02-25 : 09:49:01

The problem is that you will need to convert the type from smallint to varchar and then do the concatenation in order to print it out in the format you wish to see it in. Below is a code snippet that highlights how this should be done. Just incorporate the logic in your code.

DECLARE @i1 smallint,
@i2 smallint,
@i3 smallint,
@i1v varchar(100),
@i2v varchar(100),
@i3v varchar(100)

SET @i1=100
SET @i2=123
SET @i3=999

SET @i1v =convert(varchar(100) , @i1)
SET @i2v =convert(varchar(100) , @i2)
SET @i3v =convert(varchar(100) , @i3)

select @i1v + @i2v + @i3v

VIG
Yak Posting Veteran

86 Posts

Posted - 2006-02-25 : 09:55:36

select n1*10000000 + n2*10000 + n3, -- first variant
cast(n1 as varchar) +cast(n2 as varchar)+cast(n3 as varchar) -- second variant
from
(select 212 n1,304 n2, 7788 n3
) t

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-25 : 11:24:00

quote:
Originally posted by VIG

select n1*10000000 + n2*10000 + n3, -- first variant
cast(n1 as varchar) +cast(n2 as varchar)+cast(n3 as varchar) -- second variant
from
(select 212 n1,304 n2, 7788 n3
) t

The first variant will fail with an Arithmetic overflow error if you try it with area code greater then 214 because it is a value larger than a integer.

The code below will cast it to bigint to be able to hold all 10 digit phone numbers.


select
	Phone = n1*000010000000 + n2*10000 + n3
from
	(select 888 n1,304 n2, 7788 n3 ) t

Results:

Phone                   
----------------------- 
8883047788

(1 row(s) affected)

CODO ERGO SUM

VIG
Yak Posting Veteran

86 Posts

Posted - 2006-02-25 : 15:52:09

quote:
Originally posted by Michael Valentine Jones
...The first variant will fail with an Arithmetic overflow error if you try it with area code greater then 214 because it is a value larger than a integer....

I know.This was only idea

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-25 : 19:28:24

For converting to a varchar, you have to allow for the possibility that you may need a leading zero. It you convert the number 0005 to varchar you end up with a string '5'.

Since your source data is smallint, this code will convert to strings with leading zeros to give you a normal phone number fromat.


select
	Phone =
	right(100000+n1,3) + right(100000+n2,3) + right(100000+n3,4) 
from
	(select 888 n1,014 n2, 0001 n3 ) t

Results:


Phone      
---------- 
8880140001

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources