希望对你有用-提取数字 IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^0-9]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'') END RETURN @S END GO --测试 PRINT DBO.GET_NUMBER('呵呵ABC123ABC') GO --123 -------------------------------------------------------------------- --提取英文 IF OBJECT_ID('DBO.GET_STR') IS NOT NULL DROP FUNCTION DBO.GET_STR GO CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^a-z]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'') END RETURN @S END GO --测试 PRINT DBO.GET_STR('呵呵ABC123ABC') GO -------------------------------------------------------------------- --提取中文 IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL DROP FUNCTION DBO.CHINA_STR GO CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^吖-座]%',@S) > 0 SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'') RETURN @S END GO PRINT DBO.CHINA_STR('呵呵ABC123ABC') GO
不是xml不支持,而是你的xml的编码有问题,没有用unicode编码吧
CREATE TABLE ErrorMessage ( NA VARCHAR(20)) INSERT INTO ErrorMessage VALUES('') INSERT INTO ErrorMessage VALUES('★') INSERT INTO ErrorMessage VALUES('△') GO
CREATE TABLE Erro ( NA1 VARCHAR(20)) INSERT INTO Erro VALUES('实打实的大★厦') INSERT INTO Erro VALUES('实打大★厦') GO DECLARE @REPLACECMD VARCHAR(2000)='NA1' SELECT @REPLACECMD ='REPLACE('+ @REPLACECMD+','''+NA+''' ,'''')' FROM ErrorMessage print @REPLACECMD DECLARE @SQLCMD VARCHAR(4000)='' SET @SQLCMD = 'select '+@REPLACECMD+'AS NECOL FROM Erro' EXEC (@SQLCMD)
--NECOL 实打实的大厦 实打大厦
我是用数据库中查询出的数据拼接xml文件的,请问怎么按你说的unicode编码
这.... 貌似不同的字体有不同的形式....SSMS 里面复制不下来。
类似 Wingdings 里面那些符号,和字体有关。
通过此函数过滤掉特殊字符 public static string ReplaceLowOrderASCIICharacters(string tmp) { StringBuilder info = new StringBuilder(); foreach (char cc in tmp) { int ss = (int)cc; if (((ss >= 0) && (ss <= 8)) || ((ss >= 11) && (ss <= 12)) || ((ss >= 14) && (ss <= 32))) info.AppendFormat(" ", ss);//&#x{0:X}; else info.Append(cc); } return info.ToString(); }
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
--------------------------------------------------------------------
--提取英文
IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--提取中文
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN @S
END
GO
PRINT DBO.CHINA_STR('呵呵ABC123ABC')
GO
INSERT INTO ErrorMessage VALUES('')
INSERT INTO ErrorMessage VALUES('★')
INSERT INTO ErrorMessage VALUES('△')
GO
CREATE TABLE Erro ( NA1 VARCHAR(20))
INSERT INTO Erro VALUES('实打实的大★厦')
INSERT INTO Erro VALUES('实打大★厦')
GO
DECLARE @REPLACECMD VARCHAR(2000)='NA1'
SELECT @REPLACECMD ='REPLACE('+ @REPLACECMD+','''+NA+''' ,'''')' FROM ErrorMessage
print @REPLACECMD
DECLARE @SQLCMD VARCHAR(4000)=''
SET @SQLCMD = 'select '+@REPLACECMD+'AS NECOL FROM Erro'
EXEC (@SQLCMD)
--NECOL
实打实的大厦
实打大厦
public static string ReplaceLowOrderASCIICharacters(string tmp)
{
StringBuilder info = new StringBuilder();
foreach (char cc in tmp)
{
int ss = (int)cc;
if (((ss >= 0) && (ss <= 8)) || ((ss >= 11) && (ss <= 12)) || ((ss >= 14) && (ss <= 32)))
info.AppendFormat(" ", ss);//&#x{0:X};
else info.Append(cc);
}
return info.ToString();
}