我本来是ACCESS的,
SQL语句是这样:InStr(1,lcase(director),lcase('"&Star&"'),0)<>0
换成SQL SERVER之后,SQL语句我也跟着换:
CHARINDEX(Lower(myinfo),Lower('"&reInfo&"'))<>0
但是 SQL语句出错了,
Microsoft OLE DB Provider for SQL Server 错误 '80040e07'
函数 lower 的参数 1 的数据类型 ntext 无效。
什么原因呢?
------解决方案--------------------
- SQL code
将大写字符数据转换成小写后返回字符表达式。语法 LOWER ( character_expression ) 参数character_expression 字符数据类型或二进制数据类型的表达式，或者可以隐式转换为 nvarchar 或 ntext 数据类型的表达式。
------解决方案--------------------
- SQL code
LOWER将大写字符数据转换为小写字符数据后返回字符表达式。语法LOWER ( character_expression )参数character_expression是字符或二进制数据表达式。character_expression 可以是常量、变量或列。character_expression 必须是可以隐性转换为 varchar 的数据类型。否则，使用 CAST 显式转换 character_expression。返回类型varchar示例下例在选择价格位于 $11 和 $20 间的书名时，使用 LOWER 函数、UPPER 函数并把 UPPER 函数嵌套在 LOWER 函数内。USE pubsGOSELECT LOWER(SUBSTRING(title, 1, 20)) AS Lower, UPPER(SUBSTRING(title, 1, 20)) AS Upper, LOWER(UPPER(SUBSTRING(title, 1, 20))) As LowerUpperFROM titlesWHERE price between 11.00 and 20.00GO下面是结果集：Lower Upper LowerUpper -------------------- -------------------- -------------------- the busy executive's THE BUSY EXECUTIVE'S the busy executive's cooking with compute COOKING WITH COMPUTE cooking with compute straight talk about STRAIGHT TALK ABOUT straight talk about silicon valley gastr SILICON VALLEY GASTR silicon valley gastr secrets of silicon v SECRETS OF SILICON V secrets of silicon v prolonged data depri PROLONGED DATA DEPRI prolonged data depri fifty years in bucki FIFTY YEARS IN BUCKI fifty years in bucki sushi, anyone? SUSHI, ANYONE? sushi, anyone? (8 row(s) affected)
------解决方案--------------------
- SQL code
CHARINDEX(myinfo,'reInfo') <>0
------解决方案--------------------
不能直接操作 ntext,text之类数据，这类数据有专用函数操作，比如textptr等等。