Oracle 11g-使用回车换行符在CLOB中查找记录 [英] Oracle 11g - Find Records in a CLOB with Carriage Return Line Feed
问题描述
我正在使用Oracle 11g.我正在尝试在CLOB字段上执行查找和替换"功能(使用REPLACE).
I am on Oracle 11g. I am trying to do a Find and Replace functionality on a CLOB field (using REPLACE).
现在我的CLOB中的数据中包含CRLF，在我想找到一个包含CRLF的字符串之前，替换就可以正常工作.也许最好用示例来解释:
Now the data in my CLOB has CRLFs in them, the replace works just fine until I want to find a string that contains CRLFs. Perhaps this would be best explained by example:
So say the text in my field is:
----------------------------------
Hi there this is some text
that has CRLFS in it.
Some other text that
is there also.
Have a nice day
现在我要做的是替换所有包含CRLF的情况:
Now what I want to do is replace all occurrences of this INCLUDING the CRLFs:
Search Text
--------------------------------------------------------------------------------
Some other text that
is there also.
包含以下文本:CRLF:
With this text INCLUING the CRLFs:
Replace Text
------------------------------------
Some other text that
has some new text that is there also.
所以可能是:
----------------------------------
Hi there this is some text
that has CRLFS in it.
Some other text that
has some new text that is there also.
Have a nice day
现在我正在存储过程中执行此操作，并且搜索文本"和替换文本"作为变量进入，但是当我尝试说出％||的位置时ReplaceText || '％'，它返回0行.
Now I am doing this in a stored procedure and the Search Text and Replace Text come in as variables, but When I try and say where like % || ReplaceText || '%' it returns 0 rows.
有人知道怎么做吗?
这是我的存储过程(iOldResponsibilities是搜索文本，iNewResponsibilities是替换文本:
Here is my stored procedure (iOldResponsibilities is the Search Text, iNewResponsibilities is the replace text:
PROCEDURE FindReplaceResponsibilities (
iOldResponsibilities IN JP_JOB_FAMILIES.RESPONSIBILITIES%TYPE,
iNewResponsibilities IN JP_JOB_FAMILIES.RESPONSIBILITIES%TYPE,
oNumRowsUpdated OUT INTEGER
)
IS
BEGIN
oNumRowsUpdated := 0;
SAVEPOINT sp_jf_findrepresp;
-- If there is no old text to search for then,
-- append the new text to the end of every row.
-- Else replace all occurrences of the old text with the new text
IF iOldResponsibilities IS NULL THEN
UPDATE JP_JOB_FAMILIES
SET RESPONSIBILITIES = RESPONSIBILITIES || iNewResponsibilities;
oNumRowsUpdated := SQL%ROWCOUNT;
ELSE
UPDATE JP_JOB_FAMILIES
SET RESPONSIBILITIES = REPLACE(RESPONSIBILITIES, iOldResponsibilities, iNewResponsibilities)
WHERE RESPONSIBILITIES like '%' || iOldResponsibilities || '%';
-- I have also tried this:
--WHERE dbms_lob.instr(RESPONSIBILITIES, TO_CLOB(iOldResponsibilities)) > 0; -- This is a LIKE for CLOBS
oNumRowsUpdated := SQL%ROWCOUNT;
END IF;
RETURN;
EXCEPTION
WHEN OTHERS THEN
BEGIN
oNumRowsUpdated := -1;
ROLLBACK TO sp_jf_findrepresp;
dbms_output.put_line('error: ' || sqlerrm);
RETURN;
END;
END FindReplaceResponsibilities;
文本来自asp.net应用程序(c#)，为字符串值:
The Text is coming from an asp.net application (c#) as String values:
public int FindReplaceJobFamilyResponsibilities(String oldResponsibilities, String newResponsibilities, IDbTransaction transaction = null)
{
using (IDbCommand cmd = this._dataHelper.GetStoredProcedure(_connectionString,
"JP_JOBFAM_PKG.FindReplaceResponsibilities", true))
{
_dataHelper.SetParameterValue(cmd, "iOldResponsibilities", oldResponsibilities);
_dataHelper.SetParameterValue(cmd, "iNewResponsibilities", newResponsibilities);
DataHelperBase.VerifyParameters(cmd.Parameters, false);
base.SetExecuteConnection(cmd, transaction);
_dataHelper.ExecuteNonQuery(cmd);
return Convert.ToInt32(_dataHelper.GetParameterValue(cmd, "oNumRowsUpdated"));
}
}
推荐答案
原来是一种不良数据的情况.我的测试数据库中的数据已损坏，只有LF而不是CRLF.
Turns out to be a case of bad data. The data in my Test DB was corrupted and only had LFs instead of CRLFs.
GIGO:-)
感谢您的所有帮助
哦，顺便说一句，在我的代码示例中，我使用了INSTR函数而不是like函数.如果用户在文本中输入％进行搜索，则可能会使like语句弄乱. (由于％可能是我数据中的有效字符，因此无法将其过滤掉)
Oh and by the way In my code example I went with the INSTR function instead of the like function. If the user entered a % in the text to search through that might have messed up the like statement. (Can't filter those out because % might be a valid character in my data)
这是最终代码:
PROCEDURE FindReplaceResponsibilities (
iOldResponsibilities IN JP_JOB_FAMILIES.RESPONSIBILITIES%TYPE,
iNewResponsibilities IN JP_JOB_FAMILIES.RESPONSIBILITIES%TYPE,
oNumRowsUpdated OUT INTEGER
) 是
开始 oNumRowsUpdated:= 0;
BEGIN oNumRowsUpdated := 0;
SAVEPOINT sp_jf_findrepresp;
-- If there is no old text to search for then,
-- append the new text to the end of every row.
-- Else replace all occurrences of the old text with the new text
IF iOldResponsibilities IS NULL THEN
UPDATE JP_JOB_FAMILIES
SET RESPONSIBILITIES = RESPONSIBILITIES || iNewResponsibilities;
oNumRowsUpdated := SQL%ROWCOUNT;
ELSE
UPDATE JP_JOB_FAMILIES
SET RESPONSIBILITIES = REPLACE(RESPONSIBILITIES, iOldResponsibilities, iNewResponsibilities)
WHERE dbms_lob.instr(RESPONSIBILITIES, iOldResponsibilities) > 0;
oNumRowsUpdated := SQL%ROWCOUNT;
END IF;
RETURN;
EXCEPTION
WHEN OTHERS THEN
BEGIN
oNumRowsUpdated := -1;
ROLLBACK TO sp_jf_findrepresp;
dbms_output.put_line('error: ' || sqlerrm);
RETURN;
END;
END FindReplaceResponsibilities;
END FindReplaceResponsibilities;
我的应用程序中的代码很好:
The code from my application was fine:
public int FindReplaceJobFamilyResponsibilities(String oldResponsibilities, String newResponsibilities, IDbTransaction transaction = null)
{
using (IDbCommand cmd = this._dataHelper.GetStoredProcedure(_connectionString,
"JP_JOBFAM_PKG.FindReplaceResponsibilities", true))
{
_dataHelper.SetParameterValue(cmd, "iOldResponsibilities", oldResponsibilities);
_dataHelper.SetParameterValue(cmd, "iNewResponsibilities", newResponsibilities);
DataHelperBase.VerifyParameters(cmd.Parameters, false);
base.SetExecuteConnection(cmd, transaction);
_dataHelper.ExecuteNonQuery(cmd);
return Convert.ToInt32(_dataHelper.GetParameterValue(cmd, "oNumRowsUpdated"));
}
}
这篇关于Oracle 11g-使用回车换行符在CLOB中查找记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
