Avoid putting double quotes at the beginning and end of a string

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

select * from tblname where test = 'default'


I don't need quotes to replace "default".

I want to

select * from tblname where test = 0


is there a way to do this?


source to share

3 answers

I am assuming the field test

is of text type (varchar, char, etc.).

First: update the table to contain "0" where it contains "default".

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


Then: select all lines with "0" in them. You cannot leave quotes behind because they are part of the SQL syntax.

SELECT * FROM tblname WHERE test = '0'


To get away from the quotes, you must rotate the field to numeric (int, float or the like).



I think you are better off using formatted strings

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


You should also ask yourself why you are generating embedded SQL on the fly and not using stored procedures, as this can happen with SQL injection attacks if you don't sanitize the input.



There are several things you can do to perform simple string substitutions, however I highly recommend you learn about parameterization to ensure injection safety and query plan reuse.

The options also benefit by avoiding the quotes issue, so just replace 'default'

with (for example) @p1

and you're sorted. This replacement could be:

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



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


From C # it will be DbCommand

with parameters; from T-SQL you can consider sp_ExecuteSQL

. Anyway, you would like:

select * from tblname where test = @p1


And put @ p1 as a parameter. So from C #:

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


Or from TSQL:

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


(replace varchar(50)

with correct type)



All Articles