CREATE TABLE tb (id INT IDENTITY(1,1) PRIMARY KEY, a VARCHAR(20)) INSERT INTO tb(a) SELECT 'dha€1' UNION ALL SELECT 'ggf' UNION ALL SELECT 'f€€dhj'DECLARE @i INT SET @i = 1 DECLARE @j INT DECLARE @sql VARCHAR(MAX) SET @sql = 'SELECT * FROM dbo.tb WHERE id IN(SELECT id FROM ( SELECT xid,id,a FROM ( select id, ' SELECT @j = MAX(LEN(a)) FROM tb WHILE @i <= @j BEGIN SET @sql = @sql + 'ASCII(substring(a,' + CONVERT(VARCHAR(2), @i) + ',1)) as [' + CONVERT(VARCHAR(2), @i) + '],' SET @i = @i + 1 END SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from tb )p UNPIVOT (a FOR xid IN (' SET @i = 1 WHILE @i <= @j BEGIN SET @sql = @sql + '[' + CONVERT(VARCHAR(2), @i) + '],' SET @i = @i + 1 END SET @sql = LEFT(@sql, LEN(@sql) - 1) + ')) AS pvt) b WHERE b.a>127) ' --PRINT @sql EXEC (@sql)DROP TABLE dbo.tb id a ----------- -------------------- 1 dha€1 3 f€€dhj(2 行受影响) 是这样不……
INSERT INTO tb(a)
SELECT 'dha€1' UNION ALL SELECT 'ggf' UNION ALL SELECT 'f€€dhj'DECLARE @i INT
SET @i = 1
DECLARE @j INT
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT * FROM dbo.tb WHERE id IN(SELECT id FROM ( SELECT xid,id,a FROM ( select id, '
SELECT @j = MAX(LEN(a))
FROM tb
WHILE @i <= @j
BEGIN
SET @sql = @sql + 'ASCII(substring(a,' + CONVERT(VARCHAR(2), @i)
+ ',1)) as [' + CONVERT(VARCHAR(2), @i) + '],'
SET @i = @i + 1
END
SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from tb )p UNPIVOT (a FOR xid IN ('
SET @i = 1
WHILE @i <= @j
BEGIN
SET @sql = @sql + '[' + CONVERT(VARCHAR(2), @i) + '],'
SET @i = @i + 1
END
SET @sql = LEFT(@sql, LEN(@sql) - 1) + ')) AS pvt) b WHERE b.a>127) '
--PRINT @sql
EXEC (@sql)DROP TABLE dbo.tb
id a
----------- --------------------
1 dha€1
3 f€€dhj(2 行受影响)
是这样不……
T-SQL没有 实现对 REGEXP的支持吧, 我看了好多都是调用JS 或者 CLR实现的服务器上不允许