如何在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屋！