CODEWITHSUNDEEP
sql-server
HonzaB
HonzaB

Reputation: 7335

Cannot concatenate string columns

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'

Upvotes: 1

Views: 284

Answers (3)

user1429080
user1429080

Reputation: 9166

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.

Upvotes: 1

Mahesh.K
Mahesh.K

Reputation: 901

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)

Upvotes: 0

Jeff Torres
Jeff Torres

Reputation: 1

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]

Upvotes: 0

Related Questions