Concat problem.

Having eventually got the query from hell to work (see thread below) I now need to fiddle about with some of the output. I need to concat 3 fields (1 numeric and 2 character) in the following format:

Charfield1numericCharfield2

How would I do this a) In the normal world? and b)in the context of the query in the thread below?

Any ideas? So far all I've managed to do is destroy all the useful data I've brought back or create SQL full of errors.

This is MSSQL right? The following should work on MSSQL...I've tested it on 2000 and 6.5....

select (Charfield1 + convert(varchar(50),numeric) + Charfield2) as combined from table

Let me know how it goes....

Matt,

Cheers for that, it's almost there now! The only remaining problem is a formatting problem - if the numeric field is a single digit eg. 3 I need to display 03 in string, if it's multiple digits (10+) then I don't need the leading zero.

Thoughts?

Cheers,

Hugh

Try this..it's not pretty, but it works

select (Charfield1 +
CASE WHEN numeric > 9 THEN CONVERT(varchar(3),numeric)
WHEN numeric < 10 THEN '0' +
CONVERT(varchar(3),numeric) END + Charfield2) as combined from table

Matt,

Problem solved! Thanks for your help.

Cheers,

Hugh

Not a problem...glad to be of help!