逃离转义字符不起作用 - SQL LIKE操作 [英] Escaping the escape character does not work – SQL LIKE Operator
本文介绍了逃离转义字符不起作用 - SQL LIKE操作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我已经使用 \
为转义字符
为 LIKE
运营商。我逃避以下四个大字
1 %
2 [
3 ]
4 _
当我通过转义字符的输入,查询不返回值。我怎样才能使它工作?
数据插入
定义@Text VARCHAR(MAX)
SET @Text ='错误\\\ \\ C:\工具箱\线180'
INSERT INTO账户(账户号码,AccountType,持续时间,ModifiedTime)
VALUES(198,@文本,1,GETDATE())
code
静态无效的主要(字串[] args)
{
字符串searchValue1 = @错误\\\ \\ C:\工具箱\线180;
字符串searchValue2 = @55555;
字符串RESULT1 = DisplayTest(searchValue1);
字符串结果2 = DisplayTest(searchValue2);
Console.WriteLine(RESULT1 ::+ RESULT1);
Console.WriteLine(结果2 ::+结果2);
到Console.ReadLine();
}}
私人静态字符串DisplayTest(字符串searchValue)
{
searchValue = CustomFormat(searchValue);
字符串测试=的String.Empty;
字符串的connectionString =数据源=;初始目录= LibraryReservationSystem;集成安全性= TRUE;连接超时= 30;
使用(SqlConnection的连接=新的SqlConnection(的connectionString))
{
connection.Open();
字符串的CommandText = @SELECT AccountType,*
从账户
WHERE AccountType LIKE @input ESCAPE'\';
使用(SqlCommand的命令=新的SqlCommand(CommandText中,连接))
{
command.CommandType = System.Data.CommandType.Text;
command.Parameters.AddWithValue(@输入,%+ searchValue +%);
使用(SqlDataReader的读卡器= Command.ExecuteReader却())
{
如果(reader.HasRows)
{
而(reader.Read())
{
测试= reader.GetString(0);
}
}
}
}
}
回归测试;
}
私人静态字符串CustomFormat(字符串输入)
{
输入= input.Replace(@%,@\%);
输入= input.Replace(@[,@\ [);
输入= input.Replace(@],@\]);
输入= input.Replace(@_,@\ _);
//输入= input.Replace(@\,@\\);
返回输入;
}
参考文献:
- <一个href="http://stackoverflow.com/questions/439495/how-can-i-escape-square-brackets-in-a-like-clause">How我可以逃脱方括号在LIKE子句?
- <一个href="http://stackoverflow.com/questions/1428197/how-to-escape-a-string-for-use-with-the-like-operator-in-sql-server">How逃脱一个字符串在SQL Server中与LIKE操作者使用?
解决方案
修改你的 CustomFormat
的方法是这样的:
私有静态字符串CustomFormat(字符串输入)
{
输入= input.Replace(@\,@\\);
输入= input.Replace(@%,@\%);
输入= input.Replace(@[,@\ [);
输入= input.Replace(@],@\]);
输入= input.Replace(@_,@\ _);
返回输入;
}
I have used \
as escape character
for LIKE
operator. I am escaping following four characters
1 %
2 [
3 ]
4 _
When I pass the escape character as input, the query does not return a value. How can I make it work?
Data Insert
DECLARE @Text VARCHAR(MAX)
SET @Text = 'Error \\\ \\ C:\toolbox\line 180'
INSERT INTO Account (AccountNumber,AccountType,Duration,ModifiedTime)
VALUES (198,@Text,1,GETDATE())
CODE
static void Main(string[] args)
{
string searchValue1 = @"Error \\\ \\ C:\toolbox\line 180";
string searchValue2 = @"55555";
string result1 = DisplayTest(searchValue1);
string result2 = DisplayTest(searchValue2);
Console.WriteLine("result1:: " + result1);
Console.WriteLine("result2:: " + result2);
Console.ReadLine();
}}
private static string DisplayTest(string searchValue)
{
searchValue = CustomFormat(searchValue);
string test = String.Empty;
string connectionString = "Data Source=.;Initial Catalog=LibraryReservationSystem;Integrated Security=True;Connect Timeout=30";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string commandText = @"SELECT AccountType,*
FROM Account
WHERE AccountType LIKE @input ESCAPE '\'";
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.CommandType = System.Data.CommandType.Text;
command.Parameters.AddWithValue("@input", "%" + searchValue + "%");
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
test = reader.GetString(0);
}
}
}
}
}
return test;
}
private static string CustomFormat(string input)
{
input = input.Replace(@"%", @"\%");
input = input.Replace(@"[", @"\[");
input = input.Replace(@"]", @"\]");
input = input.Replace(@"_", @"\_");
//input = input.Replace(@"\", @"\\");
return input;
}
REFERENCE:
- How can I escape square brackets in a LIKE clause?
- How to escape a string for use with the LIKE operator in SQL Server?
解决方案
Modify your CustomFormat
method like this:
private static string CustomFormat(string input)
{
input = input.Replace(@"\", @"\\");
input = input.Replace(@"%", @"\%");
input = input.Replace(@"[", @"\[");
input = input.Replace(@"]", @"\]");
input = input.Replace(@"_", @"\_");
return input;
}
这篇关于逃离转义字符不起作用 - SQL LIKE操作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文