asked on

T-sql question

Hello,

I have a database with a text column with a value of:

Designing an Organization''''s Structure

The program inserted extra apostrophes, so there are 4 apostrophes in it.

If I try and do a SELECT however to find those records with that value, using...

SELECT *
FROM tblTrackDetail
WHERE (TrackDetail_Description = 'Designing an Organization''''s Structure')

... it returns nothing.

Any suggestions?

Thomas

I think the problem is that a single quote is used to start a string, by adding another apostrophe, you are escaping the first one
So the comparison that is done by this query is: TackDetail_Description = Designing an Organization''s Structure -> FALSE

Ryan Chong

The program inserted extra apostrophes, so there are 4 apostrophes in it.

so it seems more like a data insertion mistake to me instead.

so if the data is exactly looks like this:

Designing an Organization''''s Structure

Open in new window

i would suggest to do some data patching to make it such as:

Designing an Organization's Structure

Open in new window

and then adjust your Select SQL statement accordingly. (double the single quotes), like:

SELECT        *
FROM            tblTrackDetail
WHERE        (TrackDetail_Description = 'Designing an Organization''s Structure')

Open in new window

HainKurt

you have to put 8 ' :)

SELECT        *
FROM            tblTrackDetail
WHERE        (TrackDetail_Description = 'Designing an Organization''''''''s Structure')

Open in new window

Steve Bohler

ASKER

Unfortunately, that does not work.

HainKurt

Unfortunately, that does not work.

if we have 4 ' inside db, it works!

copy paste the data from db as is, so we can have a look...

Sharath S

create table test (col varchar(100))
SET QUOTED_IDENTIFIER OFF;
insert into test values ("Designing an Organization''''s Structure")
SET QUOTED_IDENTIFIER ON;

select * from test
-- Designing an Organization''''s Structure

Open in new window

Steve Bohler

ASKER

Here is another value copied and pasted from SSMS:

Building Your ''''Business Savvy''''

If I query...

select * from tbltrackdetail where (trackdetail_description = 'Building Your ''''Business Savvy''''')

no records are returned...

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

can you update your table as

remove extra '

update tbltrackdetail
   set trackdetail_description=replace(trackdetail_description,'''''','''')
 where trackdetail_description like '%''''%'

Open in new window

or

replace them with "

update tbltrackdetail
   set trackdetail_description=replace(trackdetail_description,'''''','"')
 where trackdetail_description like '%''''%'

Open in new window

multiple times, until you dont get any records updated?

Ryan Chong

yea... looks weird to have extra single quotes which mean the content lose its meaning.

HainKurt

I guess op has a strange algorithm to update records and before updating, it replaces ' with ''
and at the end, at each update it gets duplicated...

it does not make sense :)

the update procedure should be fixed...

Steve Bohler

ASKER

There was both a bug in the code that did two escapes of any apostrophes

Ryan Chong

There was both a bug in the code that did two escapes of any apostrophes

yea... that's why both @Huseyin and I suggested you to patch the data.