数据库系统中,用户在输入数据的时候会不小心使用全角输入,这就有可能会导致我们的程序出错,如何解决此类问题了。
下面是C#的全角转半角
/// <summary>
/// 转半角的函数(DBC case)
/// </summary>
/// <param name="input">任意字符串</param>
/// <returns>半角字符串</returns>
///<res>
///全角空格为12288,半角空格为32
///其他字符半角(33-126)与全角(65281-65374)的对应关系是:均相差65248
///</res>
public static string ToDBC(string input)
{
char[] c = input.ToCharArray();
for (int i = 0; i < c.Length; i++)
{
if (c[i] == 12288)
{
c[i] = (char)32;
continue;
}
if (c[i] > 65280 && c[i] < 65375)
c[i] = (char)(c[i] - 65248);
}
return new string(c);
}
这个是 我在网上找的 修改后的函数
报错:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@str nvarchar(4000),@flag integer )
returns nvarchar
begin
declare' at line 3函数如下:DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `test`.`u_convert`(@str NVARCHAR(4000),@flag BIT )
RETURNS NVARCHAR
BEGIN
DECLARE @pat NVARCHAR(8);
DECLARE @step INTEGER;
DECLARE @i INTEGER;
DECLARE @spc INTEGER;
IF @flag=0
BEGIN
SELECT N'%[!-~]%' INTO @pat;
SELECT -65248 INTO @step;
SELECT REPLACE(@str,N' ',N' ') INTO @str;
END
ELSE
BEGIN
SELECT N'%[!-~]%' INTO @pat;
SELECT 65248 INTO @step;
SELECT REPLACE(@str,N' ',N' ') INTO @str;
END
SELECT patindex(@pat COLLATE LATIN1_GENERAL_BIN,@str) INTO @i;
WHILE @i>0 DO
SELECT REPLACE(@str, SUBSTRING(@str,@i,1), NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step)) INTO @str;
SELECT patindex(@pat COLLATE LATIN1_GENERAL_BIN,@str) INTO @i;
END WHILE
RETURN(@str)
END $$
DELIMITER ;
下面是C#的全角转半角
/// <summary>
/// 转半角的函数(DBC case)
/// </summary>
/// <param name="input">任意字符串</param>
/// <returns>半角字符串</returns>
///<res>
///全角空格为12288,半角空格为32
///其他字符半角(33-126)与全角(65281-65374)的对应关系是:均相差65248
///</res>
public static string ToDBC(string input)
{
char[] c = input.ToCharArray();
for (int i = 0; i < c.Length; i++)
{
if (c[i] == 12288)
{
c[i] = (char)32;
continue;
}
if (c[i] > 65280 && c[i] < 65375)
c[i] = (char)(c[i] - 65248);
}
return new string(c);
}
这个是 我在网上找的 修改后的函数
报错:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@str nvarchar(4000),@flag integer )
returns nvarchar
begin
declare' at line 3函数如下:DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `test`.`u_convert`(@str NVARCHAR(4000),@flag BIT )
RETURNS NVARCHAR
BEGIN
DECLARE @pat NVARCHAR(8);
DECLARE @step INTEGER;
DECLARE @i INTEGER;
DECLARE @spc INTEGER;
IF @flag=0
BEGIN
SELECT N'%[!-~]%' INTO @pat;
SELECT -65248 INTO @step;
SELECT REPLACE(@str,N' ',N' ') INTO @str;
END
ELSE
BEGIN
SELECT N'%[!-~]%' INTO @pat;
SELECT 65248 INTO @step;
SELECT REPLACE(@str,N' ',N' ') INTO @str;
END
SELECT patindex(@pat COLLATE LATIN1_GENERAL_BIN,@str) INTO @i;
WHILE @i>0 DO
SELECT REPLACE(@str, SUBSTRING(@str,@i,1), NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step)) INTO @str;
SELECT patindex(@pat COLLATE LATIN1_GENERAL_BIN,@str) INTO @i;
END WHILE
RETURN(@str)
END $$
DELIMITER ;
http://bbs.phphubei.com/thread-4912-1-1.html
不要用@ 为定义变量! @在MYSQL中是全局变量。
str nvarchar(4000),flag integer )
现在报错
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'begin
select N'%[!-~]%' into pat;
select -65248 into st' at line 11
还有就是:
SELECT patindex(pat COLLATE LATIN1_GENERAL_BIN,str) INTO i;这个地方 patindex是不是没有这个函数啊 怎么没有变颜色 啊
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `test`.`u_convert`(str NVARCHAR(200),flag INTEGER )
RETURNS NVARCHAR(200)
BEGIN
DECLARE pat NVARCHAR(8);
DECLARE step INTEGER;
DECLARE i INTEGER;
DECLARE spc INTEGER;
IF flag=0
BEGIN
SELECT N'%[!-~]%' INTO pat;
SELECT -65248 INTO step;
SELECT REPLACE(str,N' ',N' ') INTO str;
END
ELSE
BEGIN
SELECT N'%[!-~]%' INTO pat;
SELECT 65248 INTO step;
SELECT REPLACE(str,N' ',N' ') INTO str;
END
SELECT patindex(pat COLLATE LATIN1_GENERAL_BIN,@str) INTO i;
WHILE i>0 DO
SELECT REPLACE(str, SUBSTRING(str,i,1), NCHAR(UNICODE(SUBSTRING(str,i,1))+step)) INTO str;
SELECT patindex(pat COLLATE LATIN1_GENERAL_BIN,str) INTO i;
END WHILE
RETURN(str)
END $$
DELIMITER ;
SELECT N'%[!-~1234567890#]%' INTO pat;
SELECT N'%[!-~1234567890#]%' INTO pat;
begin 改成 varchar
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'begin
select N'%[!-~1234567890#]%' into pat;' at line 12
-> ;
-> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '[0-9]
%’, ‘SUYLLGoO’)' at line 1
现在报错是:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(unicode(substring(str,i,1))+step)) into str;
select patindex(pat col' at line 31
MYSQL中没有这个函数,是不是你自己定义的啊?另外注意你的引号! 必须使用标准的ASCII的单引号 '
通过2个函数:CHARINDEX和PATINDEX
CHARINDEX:查某字符(串)是否包含在其他字符串中,返回字符串中指定表达式的起始位置。
PATINDEX:查某字符(串)是否包含在其他字符串中,返回指定表达式中某模式第一次出现的起始位置;如果在全部有效的文本和字符数据类型中没有找到该模式,则返回零。特殊:可以使用通配符!例子:
1. 查询字符串中是否包含非数字字符
SELECT PATINDEX(’%[^0-9]%’, ‘1235X461′)
SELECT PATINDEX(’%[^0-9]%’, ‘12350461′)
2. 查询字符串中是否包含数字字符
SELECT PATINDEX(’%[0-9]%’, ‘SUYLLGoO’)
SELECT PATINDEX(’%[0-9]%’, ‘SUYLLG0O’)http://blog.yoolink.com/?cat=4
-> //
ERROR 1305 (42000): FUNCTION test.PATINDEX does not exist
PATINDEX:查某字符(串)是否包含在其他字符串中,返回指定表达式中某模式第一次出现的起始位置;如果在全部有效的文本和字符数据类型中没有找到该模式,则返回零。那么在这里 我该如何修改完成这个函数的功能啊
Pattern是你要搜索的字符串,expression是被搜索的字符串。一般情况下expression是一个表中的一个字段,pattern的前后需要用“%”标记,除非你搜索的字符串在被收缩的字符串的最前面或者最后面。在mysql中那个函数 有这样的功能啊 我找找 您能告诉一下啊
一般这种情况,会到MYSQL的文档中找
函数,然后字符串函数中看看哪些函数能用。http://dev.mysql.com/doc/refman/5.1/zh/functions.html#string-functions
12.3. 字符串函数INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同,除非参数的顺序被颠倒。
LOCATE(substr,str) , LOCATE(substr,str,pos)
第一个语法返回字符串 str中子字符串substr的第一个出现位置。第二个语法返回字符串 str中子字符串substr的第一个出现位置, 起始位置在pos。如若substr 不在str中,则返回值为0。但这两个函数并不支持正则式。如果正则匹配则用 regexp
对应mysql下可以用instr或locate函数实现类似的功能
全角字符q_ucode='0123456789#',
半角字符b_ucode='0123456789#',输入字符 txt='1001#10001#010#'通过substr('1001#10001#010#',1,1)这种格式取出 单个字符,求出该字符在全角字符q_ucode中的位置kk,然后根据此位置kk,从半角字符b_ucode得出该字符对应的半角字符,最后用replace将它在输入字符txt中替换掉,从而完成全角转半角。现在我的问题是 LOCATE('1234567890#',SUBSTR('1001#10001#010#'
,1,1)) AS t2 显示为0,这该怎么办 怎么会出现这样的问题。mysql> SELECT SUBSTR('1234567890#',1,1) AS t0,SUBSTR('1234567
890#',1,2) AS t1,LOCATE('1234567890#',SUBSTR('1001#10001#010#'
,1,1)) AS t2,SUBSTR('1001#10001#010#',1,1) AS t3;
-> //
+------+------+------+------+
| t0 | t1 | t2 | t3 |
+------+------+------+------+
| 1 | 12 | 0 | 1 |
+------+------+------+------+
1 row in set (0.00 sec)
-> //
+---------------------------------------+
| LOCATE('1234567890#','1') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
SELECT new.txt INTO @ttxt;
SELECT REPLACE( @mstxt,'0','0') INTO @mstxt;
SELECT REPLACE( @mstxt,'1','1') INTO @mstxt;
SELECT REPLACE( @mstxt,'2','2') INTO @mstxt;
SELECT REPLACE( @mstxt,'3','3') INTO @mstxt;
SELECT REPLACE( @mstxt,'4','4') INTO @mstxt;
SELECT REPLACE( @mstxt,'5','5') INTO @mstxt;
SELECT REPLACE( @mstxt,'6','6') INTO @mstxt;
SELECT REPLACE( @mstxt,'7','7') INTO @mstxt;
SELECT REPLACE( @mstxt,'8','8') INTO @mstxt;
SELECT REPLACE( @mstxt,'9','9') INTO @mstxt;
SELECT REPLACE( @mstxt,'#','#') INTO @mstxt;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@mstxt,'0','0'),'1','1'),'2','2'),'3','3'),'4','4') ,'5','5'),'6','6'),'7','7') ,'8','8'),'9','9');
+-------------------------------------------------------------------------------
| REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@mstxt,'0','0'),'1','1'),'2','2'),'3','3'),'4','4') ,'5','5'),'6','6'),'7','7') ,'8','8'),'9','9') |
+-------------------------------------------------------------------------------
-------------------------------+
| 1234567890#
+-------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql>