How to insert data containing ', /, etc

Question

How to insert data containing ', /, etc

Zambrtp

SSC Veteran

Points: 257
More actions
July 25, 2003 at 7:26 am

#101593

HELP...I have a dynamic insert statment that
on occasions may have data that includs things like ticks ', and other symbols..
How can I make SQL SERVER ignore them and treat them just like a character...Ive tried many different data types...HELP

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Zambrtp,

quote:
HELP...I have a dynamic insert statment that
on occasions may have data that includs things like ticks ', and other symbols..
How can I make SQL SERVER ignore them and treat them just like a character...Ive tried many different data types...HELP

Try REPLACE (input , ' ' ', ' '' ')

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Zambrtp SSC Veteran Points: 257 More actions · Answer 2

Frank:

Would that always insert a tick ....this has only occurred once so far...if it always inserts a tick then the data would not be correct..

My insert statement passes the VALUES as ''('values','values')'' so that the insert statment is bulit as values ('values', 'value') in here is where the data had the extra tick so the insert statment read (''values', 'values')...

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Zambrtp,

quote:
Would that always insert a tick ....this has only occurred once so far...if it always inserts a tick then the data would not be correct..
My insert statement passes the VALUES as ''('values','values')'' so that the insert statment is bulit as values ('values', 'value') in here is where the data had the extra tick so the insert statment read (''values', 'values')...

sorry, too many values

Can you post an example?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Zambrtp SSC Veteran Points: 257 More actions · Answer 4

Here is how the insert ends up looking because the @parameter passes the data to the dynamic insert statement..

Insert into table ('col', 'col') Values (''dog', 'cat')

See right before the dog that extra tick is really part of the data but when it is in the insert statement it blows up...

BUT that isnt always the case...there isnt ALWAYS a tick as data...but there could be other delimiter...

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
Here is how the insert ends up looking because the @parameter passes the data to the dynamic insert statement..
Insert into table ('col', 'col') Values (''dog', 'cat')
See right before the dog that extra tick is really part of the data but when it is in the insert statement it blows up...
BUT that isnt always the case...there isnt ALWAYS a tick as data...but there could be other delimiter...

I believe SQL Server interprets '' in the data as single '. If it's part of the data the REPLACE function will make four out of two. Keeping the original intact

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Zambrtp SSC Veteran Points: 257 More actions · Answer 6

that is not a double quote in front of dog its two single ticks...

I dont want the tick at the end of the stream that would make the data invalid...

Sorry Im such a problem child...

How do I use the REPLACE function in the insert statment...

Insert into table ('col', 'col') REPLACE instead of values ('values', 'values')

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Hi Zambrtp,

quote:
that is not a double quote in front of dog its two single ticks...
I dont want the tick at the end of the stream that would make the data invalid...

Sorry, I should have placed blanks in between. I didn't mean a double quote, that should read ' ' ,a.k.a two single quotes. I don't think, it really matters, whether the tick is at the beginning, at he end or somewhere else in the string. It is there, that's enough. So, in order to get it in and out of SQL Server you MUST replace a single tick with two single ticks, two single ticks with four single ticks....

quote:
problem child...

a good song by AC/DC from the Dirty deeds album 🙂

Maybe some snippet will make things more clear

Try this in QA



DROP table test4

GO

CREATE TABLE test4 (

mytext varchar(50)

)

GO

DECLARE @myString1 varchar(50)

DECLARE @myString2 varchar(50)



SET @myString1 = '123''34'



SET @myString2 = '123''''34'



INSERT INTO Test4 (mytext) VALUES(+@myString1)

INSERT INTO Test4 (mytext) VALUES(+@myString2)



SELECT mytext FROM test4

in both cases when assigning the value to @myString1 and @myString2, if you leave out just one tick, the statements will become invalid. But when retrieving them via SELECT you only see one single tick, right?

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Zambrtp SSC Veteran Points: 257 More actions · Answer 8

Thanks Frank....

I was afraid of that...parsing the data to look for delimiters looks to be the only way ...

dlychev Grasshopper Points: 15 More actions · Answer 9

quote:
Thanks Frank....
I was afraid of that...parsing the data to look for delimiters looks to be the only way ...