sql05:能在sql里直接操作删掉含中文的记录吗
我们网站会员系统改版,原先有很多用户是用中文注册的ID,现在老大要求把这些直接用户删掉,也就是只保留注册ID里不含中文的用户,一共有十多万用户能有sql语句直接删掉的吗?
------------请高手指点----------------------
[解决办法]
--查询
select * from 表名 where 用户ID like '%[吖-座]% '
--删除
delete 表名 where 用户ID like '%[吖-座]% '
[解决办法]
或用如下生成一个汉字表.然后查 delete from 表 where charindex(汉字表.字段,用户ID) > 0
--GBK/2: GB2312 汉字，0xB0A1 - 0xF7FE (不包含 0xD7FA - 0xD7FE)
SELECT TOP 72 id=identity(int,176,1) INTO #a1 FROM SYSCOLUMNS
SELECT TOP 94 id=identity(int,161,1) INTO #a2 FROM SYSCOLUMNS
SELECT Code=CAST(a.id as binary(1))+CAST(b.id as binary(1)),
[Char]=CAST(CAST(a.id as binary(1))+CAST(b.id as binary(1)) as char(2))
INTO #1
FROM #a1 a,#a2 b
WHERE NOT (a.id=215 AND b.id> =250)
DROP TABLE #a1,#a2
--GBK/3: 扩充汉字，0x8140 - 0xA0FE (不包含xx7F)
SELECT TOP 32 id=identity(int,129,1) INTO #b1 FROM SYSCOLUMNS
SELECT TOP 191 id=identity(int,64,1) INTO #b2 FROM SYSCOLUMNS
DELETE FROM #b2 WHERE id=127
SELECT Code=CAST(a.id as binary(1))+CAST(b.id as binary(1)),
[Char]=CAST(CAST(a.id as binary(1))+CAST(b.id as binary(1)) as char(2))
INTO #2
FROM #b1 a,#b2 b
DROP TABLE #b1,#b2
--GBK/4: 扩充汉字，0xAA40 - 0xFEA0 (不包含xx7F)
SELECT TOP 85 id=identity(int,170,1) INTO #c1 FROM SYSCOLUMNS
SELECT TOP 97 id=identity(int,64,1) INTO #c2 FROM SYSCOLUMNS
DELETE FROM #c2 WHERE id=127
SELECT Code=CAST(a.id as binary(1))+CAST(b.id as binary(1)),
[Char]=CAST(CAST(a.id as binary(1))+CAST(b.id as binary(1)) as char(2))
INTO #3
FROM #c1 a,#c2 b
DROP TABLE #c1,#c2
--合并
SELECT *,[Unicode]=UNICODE([char]) INTO # FROM #1
UNION ALL
SELECT *,[Unicode]=UNICODE([char]) FROM #2
UNION ALL
SELECT *,[Unicode]=UNICODE([char]) FROM #3
--编码不连续的
SELECT *,(SELECT MIN([Unicode]) FROM # WHERE [Unicode]> A.[Unicode])
FROM # A
WHERE NOT EXISTS(
SELECT * FROM # WHERE [Unicode]=A.[Unicode]+1)
ORDER BY [Unicode]
--> 40869的汉字 （从 19968 - 40869 的 20902 个汉字 UNICODE 编码是连续的)
SELECT * FROM # WHERE [Unicode]> 40869
ORDER BY [Unicode]
DROP TABLE #1,#2,#3,#
