------------------------------------
-- Author: happyflsytone
-- Date:2008-09-27 23:45:31
-------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(col NVARCHAR(17))
Go
INSERT INTO ta
SELECT '[342142423]云南白药' UNION ALL
SELECT '[kjl''232]比特力' UNION ALL
SELECT '90ooi900''ooll安乃近' UNION ALL
SELECT '0oip0990000诺氟沙星胶囊'
GO
--Start;
with t as
(SELECT substring(a.col,b.N + 1 ,1000) as col ,b.n
FROM ta a
left join
(select top 1000 n= row_number() over(order by object_id) from sys.objects ) b
on ascii(substring(a.col,b.N,1)) < 127)
select col from t
where ascii(col) > 127
--Result:
/*
col
-----------------
云南白药
比特力
安乃近
诺氟沙星胶囊(4 行受影响)*/
--End
-- Author: happyflsytone
-- Date:2008-09-27 23:45:31
-------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(col NVARCHAR(17))
Go
INSERT INTO ta
SELECT '[342142423]云南白药' UNION ALL
SELECT '[kjl''232]比特力' UNION ALL
SELECT '90ooi900''ooll安乃近' UNION ALL
SELECT '0oip0990000诺氟沙星胶囊'
GO
--Start;
with t as
(SELECT substring(a.col,b.N + 1 ,1000) as col ,b.n
FROM ta a
left join
(select top 1000 n= row_number() over(order by object_id) from sys.objects ) b
on ascii(substring(a.col,b.N,1)) < 127)
select col from t
where ascii(col) > 127
--Result:
/*
col
-----------------
云南白药
比特力
安乃近
诺氟沙星胶囊(4 行受影响)*/
--End
解决方案 »
- group by 分组在分组?
- 帮我分析一下!
- wingate的"SOCKS Proxy server"中用户认证为什么一直用不了?100分求经验
- SQL 一列的每一行加值
- 请教:按字段拼音首字母查询的Sql语句?
- 触发器跨库更新一张表,出现错误:新事务不能登记到指定的事务处理器中
- 求SQL帮助
- 关于‘restore filelistonly from disk’在VB中使用的问题。
- 新手求教个正则表达式:提取字段里面的某个位置的数字!!!!!!!!!!!!!!!!
- reporting service 数据库身份验证
- 请问我的语句应该如何写?
- 安装sql2005在系统配置检查上不动了。高手帮忙!!
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(col NVARCHAR(17))
Go
INSERT INTO ta
SELECT '[342142423]云南白药' UNION ALL
SELECT '[kjl''232]比特力' UNION ALL
SELECT '90ooi900''ooll安乃近' UNION ALL
SELECT '0oip0990000诺氟沙星胶囊'
GOdeclare table_cur cursor for
select col from ta
--打开游标
open table_cur
--循环并提取记录
Fetch Next From table_cur into @col
While ( @@Fetch_Status=0 )
begin
while patindex('%[^吖-座]%',@col)>0
begin
set @i=patindex('%[^吖-座]%',@col)
Set @col=replace(stuff(@col,@i,1,''),'^','')
end
print @col
Fetch Next From table_cur Into @col
end--关闭游标
Close table_cur--释放游标
Deallocate table_cur --Result:
云南白药
比特力
安乃近
诺氟沙星胶囊
DROP TABLE ta
Go
CREATE TABLE ta(col NVARCHAR(17))
Go
INSERT INTO ta
SELECT '[342142423]云南白药' UNION ALL
SELECT '[kjl''232]比特力' UNION ALL
SELECT '90ooi900''ooll安乃近' UNION ALL
SELECT '0oip0990000诺氟沙星胶囊'
GOdeclare @col varchar(8000)
declare @i int
declare table_cur cursor for
select col from ta
--打开游标
open table_cur
--循环并提取记录
Fetch Next From table_cur into @col
While ( @@Fetch_Status=0 )
begin
while patindex('%[^吖-座]%',@col)>0
begin
set @i=patindex('%[^吖-座]%',@col)
Set @col=replace(stuff(@col,@i,1,''),'^','')
end
print @col
Fetch Next From table_cur Into @col
end--关闭游标
Close table_cur--释放游标
Deallocate table_cur --Result:
云南白药
比特力
安乃近
诺氟沙星胶囊
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
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(col NVARCHAR(17))
Go
INSERT INTO ta
SELECT '[342142423]云南白药' UNION ALL
SELECT '[kjl''232]比特力' UNION ALL
SELECT '90ooi900''ooll安乃近' UNION ALL
SELECT '0oip0990000诺氟沙星胶囊'
GO
select DBO.CHINA_STR(col) from ta
/*
----------------------------------------------------------------------------------------------------
云南白药
比特力
安乃近
诺氟沙星胶囊(所影响的行数为 4 行)*/
DROP TABLE ta
Go
CREATE TABLE ta(col VARCHAR(50))
Go
INSERT INTO ta
SELECT '[342142423]云南白药' UNION ALL
SELECT '[kjl''232]比特力' UNION ALL
SELECT '90ooi900''ooll安乃近' UNION ALL
SELECT '0oip0990000诺氟沙星胶囊'
GOSELECT STUFF(col,1,LEN(STUFF(xcol,CHARINDEX('?',xcol),10000,'')),'') col
FROM
(SELECT col,col COLLATE latin1_general_cs_as xcol FROM ta) x/*
云南白药
比特力
安乃近
诺氟沙星胶囊*/