Data question (Function or filter)?

24 Aug 2006 6:29 PM

Blasting Cap

Lets try this again......

I have a table with some data in it that unfortunately has a single
quote mark (') in it. One of these fields that it's in is in a field
where I pass a parameter into a stored procedure. As you can imagine,
that wreaks havoc with the sql code I'm trying to use.

As part of the table load, I thought about trying to remove the single
quote, but don't know how I can do it in TSQL.

Anyone have any ideas? I was thinking a function might be the best way
to go. All I want to do is replace it with a space or something.

Any help appreciated.

BC

24 Aug 2006 6:41 PM

David Portas

Blasting Cap wrote:

>
> I have a table with some data in it that unfortunately has a single
> quote mark (') in it. One of these fields that it's in is in a field
> where I pass a parameter into a stored procedure. As you can imagine,
> that wreaks havoc with the sql code I'm trying to use.

In that case your SQL code is either badly written or you aren't
passing parameters correctly. Quotes in column values and parameters
should cause no problems unless you are careless with dynamic SQL. The
proper way to pass parameters from your client code is using the
parameters collection.

>
> As part of the table load, I thought about trying to remove the single
> quote, but don't know how I can do it in TSQL.
>

Take a look at the REPLACE function.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

24 Aug 2006 7:11 PM

Jim Underwood

In addition to what David said...

If you call the database correctly using the paqrameters collection then
your quotes will be passed to the database correctly.

If you are using dynamic sql in your stored procedures, then you will want
to read this article to get some tips on the proper approach to use...

http://www.sommarskog.se/dynamic_sql.html

Show quote

"Blasting Cap" <goo***@christian.net> wrote in message
news:ei1X7s6xGHA.4204@TK2MSFTNGP04.phx.gbl...
> Lets try this again......
>
>
>
>
> I have a table with some data in it that unfortunately has a single
> quote mark (') in it. One of these fields that it's in is in a field
> where I pass a parameter into a stored procedure. As you can imagine,
> that wreaks havoc with the sql code I'm trying to use.
>
> As part of the table load, I thought about trying to remove the single
> quote, but don't know how I can do it in TSQL.
>
> Anyone have any ideas? I was thinking a function might be the best way
> to go. All I want to do is replace it with a space or something.
>
> Any help appreciated.
>
> BC