Replace Apostrophies??

[aljubicic]

Hi All,

I have trigger that sends data from one table to another. I need to make sure that if the data that needs to be sent across has an apostrophe, that it is replaced by two apostrophies so it adheres to the SQL syntax and that it doesn't error.

Is it possible to use the REPLACE function for this or is there some other way to get around this??
Can I write REPLACE(@VARIABLE, ''', '''') for this or won't this work??

If someone can give me anysight into this and any code on how to do this would be great.

Thanks
Anthony

 

[cheyney]

you need to use

replace(@VARIABLE, '''', '''''')


Cheyney

 

[SemperFiDownUnda]

if the data is not in a string then you don't have to worry about that.

Ie
INSERT INTO TableA (Col1, Col2,...)
SELECT ColA, ColB, ColC
FROM TableB
...

will not fail if any of TableB's columns have an ' in them.

Likewise if you have a local variable, @sLastName, that has the name "O'Conald" you don't have to change single ' to '' for this statement to work
INSERT INTO TableA ( LastName ) VALUES (@sLastName)

it is only if you build up a dynamic string and want to execute that string as a SQL statement .

 

[psnivas]

You can nullify the special meaning of the character ' with other ' preceding it.
e.g:
Insert into Table1(col1) values ('O''Conald') will work.