如何在SQL Server中插入特殊字符 [英] How can I insert a special character in sql server
问题描述
我们如何输入特殊字符,如"%?在SQL Server表中使用C#的asp.net等?
请帮助我
How we can enter the special character like , "" % ? etc in sql server table using asp.net with C#?
Please Help me
推荐答案
使用参数化查询:
Use parametrized queries:
using (SqlCommand com = new SqlCommand("INSERT INTO MyTable (message) " +
"VALUES (@M)", con))
{
com.Parameters.AddWithValue("@M", @"""%
#@?,.;"); com.ExecuteNonQuery(); }
#@?,.;"); com.ExecuteNonQuery(); }
我发现了这个 与GOOGLE (搜索词是存储特殊字符的t-sql"):
要在LIKE表达式中转义特殊字符,请在其前面加上转义字符.您可以选择与ESCAPE关键字一起使用的转义字符. (MSDN参考)
例如,使用\作为转义字符,可以转义%符号:
I found this WITH GOOGLE ( search phrase was "t-sql storing special characters"):
To escape special characters in a LIKE expression you prefix them with an escape character. You get to choose which escape char to use with the ESCAPE keyword. (MSDN Ref)
For example this escapes the % symbol, using \ as the escape char:
select * from table where myfield like '%15\% off%' ESCAPE '\'
如果您不知道字符串中将包含哪些字符,并且不想将其视为通配符,则可以在所有通配符前面加上转义字符,例如:
If you don''t know what characters will be in your string, and you don''t want to treat them as wildcards, you can prefix all wildcard characters with an escape char, eg:
set @myString = replace(
replace(
replace(
replace(@myString,'\','\\'),
'%','\%'),
'_','\_'),
'[','\[')
(请注意,您也必须转义转义字符).然后,您可以使用以下内容:
(Note that you have to escape your escape char too). Then you can use something like this:
select * from table where myfield like '%' + @myString + '%' ESCAPE '\'
还要记住为@myString变量分配更多空间,因为随着字符串替换,它将变长.
Also remember to allocate more space for your @myString variable as it will become longer with the string replacement.
这篇关于如何在SQL Server中插入特殊字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!