Concatenating help - 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

General SQL Server Forums

New to SQL Server Programming

Concatenating help

Author

Topic

Nuno1340
Starting Member

6 Posts

Posted - 2008-02-28 : 06:54:39

Hello

First post here and am looking for some help with a issue I have with concatenation.

I am looking to concatenate two columns together into one new column...

ins_type + currency

I have searched for help onthis but for some reason nothing I do works...

Any ideas or suggestions?

Thanks

Nuno

SELECT
a.deal_tracking_num AS TemplateDealNum,
a.tran_num AS TemplateTranNum,
a.reference AS TemplateName,
a.ins_type,
a.currency,
a.trade_date AS TemplateCreated,
u.template_tran_num as Misc

FROM
ab_tran a, USER_restricted_templates u
WHERE
a.tran_type = 0 AND
a.asset_type = 2 AND
a.current_flag = 1 AND
a.toolset IN (33,36,15,16) AND
a.tran_status = 15 AND
a.tran_num *= u.template_tran_num

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-28 : 07:01:52

What data types are they?

If ins_type is a number and currency is a char, you would do:


select convert(varchar(10),ins_type) + '' + currency

readysetstop
Posting Yak Master

123 Posts

Posted - 2008-02-28 : 07:02:10

If you can't do this in the front end app, try CAST.

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 07:41:15

<<I am looking to concatenate two columns together into one new column...>>

Where do you want to show your data?

Madhivanan

Failing to plan is Planning to fail

Nuno1340
Starting Member

6 Posts

Posted - 2008-02-28 : 08:50:06

quote:
Originally posted by RickD

What data types are they?

If ins_type is a number and currency is a char, you would do:

select convert(varchar(10),ins_type) + '' + currency

both are numbers... thank you

Nuno1340
Starting Member

6 Posts

Posted - 2008-02-28 : 08:52:15

quote:
Originally posted by madhivanan

<<I am looking to concatenate two columns together into one new column...>>

Where do you want to show your data?

Madhivanan

Failing to plan is Planning to fail

an additional column at the end of the query... if that makes sense

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 08:54:10

[code]select cast(cast(ins_type as varchar(5)) + cast(currency as varchar(5)) as int)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Nuno1340
Starting Member

6 Posts

Posted - 2008-02-28 : 09:03:04

quote:
Originally posted by harsh_athalye

select cast(cast(ins_type as varchar(5)) + cast(currency as varchar(5)) as int)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

thanks... when i try this i get an error message asking for a FROM clause...

SELECT
a.deal_tracking_num AS TemplateDealNum,
a.tran_num AS TemplateTranNum,
a.reference AS TemplateName,
a.ins_type,
a.currency,
a.trade_date AS TemplateCreated,
u.template_tran_num as Misc
select cast(cast(ins_type as varchar(5)) + cast(currency as varchar(5)) as int)
FROM
ab_tran a, USER_restricted_templates u
WHERE
a.tran_type = 0 AND
a.asset_type = 2 AND
a.current_flag = 1 AND
a.toolset IN (33,36,15,16) AND
a.tran_status = 15 AND
a.tran_num *= u.template_tran_num

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 09:05:15

quote:
Originally posted by Nuno1340

quote:
Originally posted by harsh_athalye

select cast(cast(ins_type as varchar(5)) + cast(currency as varchar(5)) as int)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

thanks... when i try this i get an error message asking for a FROM clause...

SELECT
a.deal_tracking_num AS TemplateDealNum,
a.tran_num AS TemplateTranNum,
a.reference AS TemplateName,
a.ins_type,
a.currency,
a.trade_date AS TemplateCreated,
u.template_tran_num as Misc,
~~select~~ cast(cast(ins_type as varchar(5)) + cast(currency as varchar(5)) as int) as SomeCol
FROM
ab_tran a, USER_restricted_templates u
WHERE
a.tran_type = 0 AND
a.asset_type = 2 AND
a.current_flag = 1 AND
a.toolset IN (33,36,15,16) AND
a.tran_status = 15 AND
a.tran_num *= u.template_tran_num

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-28 : 09:06:57

You should really write your joins using ANSI syntax. Express the *= as a LEFT OUTER JOIN. You code is clearer, standard, and easier to maintain.

- Jeff
http://weblogs.sqlteam.com/JeffS

Nuno1340
Starting Member

6 Posts

Posted - 2008-02-28 : 09:09:07

quote:
Originally posted by harsh_athalye

quote:
Originally posted by Nuno1340

quote:
Originally posted by harsh_athalye

select cast(cast(ins_type as varchar(5)) + cast(currency as varchar(5)) as int)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

thanks... when i try this i get an error message asking for a FROM clause...

SELECT
a.deal_tracking_num AS TemplateDealNum,
a.tran_num AS TemplateTranNum,
a.reference AS TemplateName,
a.ins_type,
a.currency,
a.trade_date AS TemplateCreated,
u.template_tran_num as Misc,
~~select~~ cast(cast(ins_type as varchar(5)) + cast(currency as varchar(5)) as int) as SomeCol
FROM
ab_tran a, USER_restricted_templates u
WHERE
a.tran_type = 0 AND
a.asset_type = 2 AND
a.current_flag = 1 AND
a.toolset IN (33,36,15,16) AND
a.tran_status = 15 AND
a.tran_num *= u.template_tran_num

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

i get:

Query Failed
Fatal Database Error
Unknown Database Error (156)
Incorrect syntax near the keyword 'select'

Nuno1340
Starting Member

6 Posts

Posted - 2008-02-28 : 09:12:05

quote:
Originally posted by jsmith8858

You should really write your joins using ANSI syntax. Express the *= as a LEFT OUTER JOIN. You code is clearer, standard, and easier to maintain.

- Jeff
http://weblogs.sqlteam.com/JeffS

very new to this sql world but will take note of your suggestions

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-28 : 09:26:42

You are using SQL Server, aren't you?!?


SELECT
a.deal_tracking_num AS TemplateDealNum,
a.tran_num AS TemplateTranNum,
a.reference AS TemplateName,
a.ins_type,
a.currency,
a.trade_date AS TemplateCreated,
u.template_tran_num as Misc,
cast(cast(ins_type as varchar(5)) + cast(currency as varchar(5)) as int) as SomeCol
FROM ab_tran a
left join USER_restricted_templates u
	on a.tran_num = u.template_tran_num
WHERE
a.tran_type = 0 AND
a.asset_type = 2 AND
a.current_flag = 1 AND
a.toolset IN (33,36,15,16) AND
a.tran_status = 15 AND

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources