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?
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).
source to share
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.
source to share
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:
-- TSQL
REPLACE(@cmd, '''default''', '@p1')
or
// 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
cmd.Parameters.Add(param);
Or from TSQL:
EXEC sp_ExecuteSQL @cmd, N'@p1 varchar(50)', 0
(replace varchar(50)
with correct type)
source to share