How To Concatenate - 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

How To Concatenate

Author

Topic

dknb
Starting Member

6 Posts

Posted - 2007-02-01 : 21:57:11

Need help. How to concat into 1 row from the example below:

Table Address
Block Flr Road
2 1 Green
3 - Maine
4 3 Bell
5 2 Prade

Result :
Block 2 Flr 1 Green Road
Block 3 Maine Road
Block 4 Flr 3 Bell Road
Block 5 Flr 2 Prade Road

Cheers!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 22:00:51

assuming Block & Flr are of int datatype


select 'Block ' + convert(varchar(10), Block) + ' ' +
       isnull('Flr ' + convert(varchar(10), Flr), '') + ' ' +
       Road + ' Road'
from   table

dknb
Starting Member

6 Posts

Posted - 2007-02-02 : 03:17:37

Hi KH

I did what you wrote but came out ORA-00936 : Missing expression.

Do apologies for the lack of info (and being a newbie)... Im query-ing using TOAD and that Block and FLr are VarChar.

:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-02 : 03:20:39

quote:
Originally posted by dknb

Hi KH

I did what you wrote but came out ORA-00936 : Missing expression.

Do apologies for the lack of info (and being a newbie)... Im query-ing using TOAD and that Block and FLr are VarChar.

:)

Oh Oracle DB

. Sorry I am not familiar with Oracle. The code i posted is T-SQL for MS SQL SERVER and this is a forum for Microsoft SQL Server articles, news and forums as stated in the upper right hand side of the screen.

Try posting at dbforums.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-02 : 03:27:02

if not mistaken, Oracle uses & operator for concat ? try it

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 03:33:17

Try this:

Select NVL('Block ' || Block, '') || ' ' ||
       NVL('Flr ' || Flr, '') || ' ' ||
       NVL(Road + ' Road', '')
from   table

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-02 : 03:50:28

also this forum is for ms sql only. try http://www.dbforums.com for future oracle questions.

www.elsasoft.org

dknb
Starting Member

6 Posts

Posted - 2007-02-05 : 22:33:22

Thank you, you all....
Appreciate it.
Sorry for the mix-up! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources