Avoid entering double quotes from start & ending of a string

Question

Hi,

In a database, I have a string that contains "default" written in it. I just want to replace that default with 0. I have something like:

select * from tblname where test = 'default'

I do not want quotes in the replacement for "default".

I want

select * from tblname where test = 0

is there any way to do this?

Answer 1

I think you would be better using format strings

string myVar = "0";
string sql = String.Format(@"select * from tblname where test = \"{0}\"", myVar);

You should also ask yourself why you are generating inline SQL on the fly and not using stored procedures as this is how SQL injection attacks can occur unless you sanitize the input.

Answer 2

I'm assuming the field test is of a text type (varchar, char, or the like).

First: Update the table to contain '0' where it contains 'default'.

UPDATE tblname SET test = '0' WHERE test = 'default'

Then: Select all rows with '0' in them. You can't leave off the quotes, because they are part of the SQL syntax.

SELECT * FROM tblname WHERE test = '0'

To be able to leave off the quotes, you must turn the field into a numeric one (int, float or the like).

Answer 3

There are a number of things you can do in order to effect a simple string replacement;, however, I strongly suggest that you look into parameterization, to provide both injection safety and query-plan re-use.

Parameters also benefit by avoiding the quote issue - so just replace 'default' with (for example) @p1 and you're sorted. This replace can be:

-- TSQL
REPLACE(@cmd, '''default''', '@p1')

or

// C#
.Replace(@"'default'", @"@p1")

From C#, this would a DbCommand with parameters; from T-SQL you might consider sp_ExecuteSQL. Either way, you'd want to end up with:

select * from tblname where test = @p1

And supply @p1 as the parameter. So from C#:

DbParameter param = cmd.CreateParameter();
param.Value = 0; // etc
cmd.Parameters.Add(param);

Or from TSQL:

EXEC sp_ExecuteSQL @cmd, N'@p1 varchar(50)', 0

(replace varchar(50) with the correct type)