problems joing data that contains quotes.....

How can I do a join where the data contins single quotes?
eg we have two suburbs in canberra called
O'MALLEY
and
O'CONNER
I have to make a join between two tables based on the value of the
suburb...but the join fails and always returns null when joining on these
two suburbs - other suburbs work just fine. Given that I cant change the
name of the suburbs(!) how can I make the join work:
select c.surname, homesuburb,uluACTSuburbs.code from community c join
join addresses a on a.addressid=c.addressid
left join uluACTSuburbs on homesuburb=uluACTSuburbs.description
returns null where homesuburb = O'MALLEY or O'CONNOR
Is there any way to quote the entire value before making the join?
Al Blake, Canberra, AustraliaHi
create table #test1
(
col1 varchar(50)
)
insert into #test1 values ('John')
insert into #test1 values ('Bill')
insert into #test1 values ('O''Connor')
create table #test2
(
col1 varchar(50)
)
insert into #test2 values ('Alexl')
insert into #test2 values ('Tom')
insert into #test2 values ('O''Connor')
select a.col1,b.col1 from #test1 a join #test2 b
on a.col1=b.col1
Note: If it does not help you please post DDL + sample data + expected
result
"Al Blake" <al@._delete_this_.blakes.net> wrote in message
news:uw$%23rilEFHA.3492@.TK2MSFTNGP12.phx.gbl...
> How can I do a join where the data contins single quotes?
> eg we have two suburbs in canberra called
> O'MALLEY
> and
> O'CONNER
> I have to make a join between two tables based on the value of the
> suburb...but the join fails and always returns null when joining on these
> two suburbs - other suburbs work just fine. Given that I cant change the
> name of the suburbs(!) how can I make the join work:
> select c.surname, homesuburb,uluACTSuburbs.code from community c join
> join addresses a on a.addressid=c.addressid
> left join uluACTSuburbs on homesuburb=uluACTSuburbs.description
> returns null where homesuburb = O'MALLEY or O'CONNOR
> Is there any way to quote the entire value before making the join?
> Al Blake, Canberra, Australia
>|||Doh,
I this case it was my own fault ;)
The data had been imported from word and someone had used the ACCENT
character, not single quote for O'Malley.
When I changed the character the join works prefectly without any messing
about.
Thanks for your reply anyway.
Al
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%237NCrlEFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi
> create table #test1
> (
> col1 varchar(50)
> )
> insert into #test1 values ('John')
> insert into #test1 values ('Bill')
> insert into #test1 values ('O''Connor')
> create table #test2
> (
> col1 varchar(50)
> )
> insert into #test2 values ('Alexl')
> insert into #test2 values ('Tom')
> insert into #test2 values ('O''Connor')
>
> select a.col1,b.col1 from #test1 a join #test2 b
> on a.col1=b.col1
>
> Note: If it does not help you please post DDL + sample data + expected
> result
>
>
>
> "Al Blake" <al@._delete_this_.blakes.net> wrote in message
> news:uw$%23rilEFHA.3492@.TK2MSFTNGP12.phx.gbl...
>|||Hi
Check what type of quote is actually present: ' or `
It is a normal character, so a join does not care what type of character it
is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Al Blake" <al@._delete_this_.blakes.net> wrote in message
news:uw$#rilEFHA.3492@.TK2MSFTNGP12.phx.gbl...
> How can I do a join where the data contins single quotes?
> eg we have two suburbs in canberra called
> O'MALLEY
> and
> O'CONNER
> I have to make a join between two tables based on the value of the
> suburb...but the join fails and always returns null when joining on these
> two suburbs - other suburbs work just fine. Given that I cant change the
> name of the suburbs(!) how can I make the join work:
> select c.surname, homesuburb,uluACTSuburbs.code from community c join
> join addresses a on a.addressid=c.addressid
> left join uluACTSuburbs on homesuburb=uluACTSuburbs.description
> returns null where homesuburb = O'MALLEY or O'CONNOR
> Is there any way to quote the entire value before making the join?
> Al Blake, Canberra, Australia
>