Cannot concatenate string columns

Question

I'd like to concatenate three columns - street, street number and city to one column "adress". The strange thing is that I cannot do it for some reason.

This is what I have tried so far:

SELECT      street,
            street_num,
            city,
            isnull(street,'') + '' + isnull(street_num,'') + '' + isnull(city,'') AS tst1, --doesnt work
            concat(isnull(street,''),' ',isnull(street_num,''), ' ', isnull(city,'')) AS tst2, --doesnt work
            (street_num + ' ' + street) AS tst3, --does work
            (street_num + ' ' + city) AS tst4, --does work
            (city + ' ' + street) AS tst5 --doesnt work
FROM        [DB].[dbo].[adresses]

Note that + or concat doesnt work, it only shows the first column, street in these cases. However, if I start with street number and add street or city, it does work. But if I try to add third column, it is not shown.

If it helps, the table was pulled from Oracle by OPENQUERY and the table structure is as follows:

street VARCHAR(100), null
street_num VARCHAR(50), null
city VARCHAR(100), null

I am on MSSQL 2014.

EDIT

As asked in the comments, i cant show the data as I am dealing with addresses of our customers. Below are two dummy records plus expected result (adress) as example:

street            | street_num | city   | adress
--------------------------------------------------------------------
avenida pino alto | 45         | avila  | avenida pino alto 45 avila  
rue de abaixo     | 86         | madrid | rue de abaixo 86 madrid

Furthermore, If i copy the records and do something like this, it works of course.

SELECT 'avenida pino alto' + ' ' + '45' + ' ' + 'avila'

you could extract some of them (two rows) and change manually some values... Apparently your query is correct (as far as I know) — etsa
– etsa, Commented Apr 4, 2017 at 8:42
@HonzaB:please paste some dummy sample data and expected rsult — TheGameiswar
– TheGameiswar, Commented Apr 4, 2017 at 8:44
try isnull(CAST(street as varchar) ,'')+' '+isnull(CAST(street_num as varchar) ,'')+' '+isnull(CAST(cityas varchar) ,'') — Nikhil Goud
– Nikhil Goud, Commented Apr 4, 2017 at 8:48

user1429080 · Accepted Answer · 2017-04-04 09:13:10Z

1

Based on comments, it seems that your street column contains some char/data that causes problems.

I have no idea what it could be, but you can try to find out like this:

select top 10
    street,
    len(street) as streetCharLen,
    cast(street as varbinary(500)) as streetBytes
from [DB].[dbo].[adresses]

Then compare what the different columns tell you.

Here's a quick sample:

declare @t table (
    id int,
    thestring varchar(50)
)

insert into @t values (1, 'test')

select thestring,
    len(thestring) as slen,
    cast(thestring as varbinary(100)) as sbytes
from @t

If in this sample, the slen is not 4, or the sbytes contains something that does not map back to one of the characters that I see when selecting, then something is wrong with the string.

edited Apr 4, 2017 at 9:13

answered Apr 4, 2017 at 9:05

user1429080

9,1764 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HonzaB Over a year ago

All right, so there were some funny addresses. But that actually led me back to Oracle, where were some strange things happening with these columns. Once I cleaned it, it works like a charm. Thank you!

Jeff Torres · Accepted Answer · 2017-04-04 08:52:25Z

0

Use convert(varchar,[exp]):

SELECT      street,
            street_num,
            city,
            isnull(convert(varchar,street),'') + '' + isnull(convert(varchar,street_num),'') + '' + isnull(convert(varchar,city),'') AS tst1

FROM        [DB].[dbo].[adresses]

answered Apr 4, 2017 at 8:52

Jeff Torres

1

Comments

Mahesh.K · Accepted Answer · 2017-04-04 08:55:31Z

0

Try as follows.

    select isnull((convert varchar(250),street),'')+isnull((convert varchar(250),[street number]),'')+
    isnull((convert varchar(250),[city]),'') as 'Adress'
    from .......(your query)

answered Apr 4, 2017 at 8:55

Mahesh.K

9016 silver badges16 bronze badges

Collectives™ on Stack Overflow

Cannot concatenate string columns

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related