好多字段中会有不是空格的空字符,但又不一定是制表符,弄得整个表很乱,所以下面最后一个[]中就是这个字符的通配符了,本身是属性值,没法选中,但可以直接复制匹配。
SELECT DISTINCT a
FROM tb_1WHERE a IS NOT NULL
AND a LIKE '%[吖-座]%'
AND a LIKE '%[]%'
提取方法
DECLARE @n INT
SELECT @n = COUNT(DISTINCT DoctorName)
FROM [MY_DY].[dbo].[table1]
WHERE [DoctorName] IS NOT NULL
AND [DoctorName] LIKE '%[^吖-座/\()]%'
AND [DoctorName] LIKE '%[吖-座]%'
DECLARE @s NVARCHAR(100),
@i INT,
@s2 NVARCHAR(100),
@a INT,
@q FLOAT,
@e FLOAT
SET @s = ''
SET @a = 0
SET @e = 1
WHILE @a < @n
BEGIN
SELECT @s = DoctorName
FROM [MY_DY].[dbo].[table1]
WHERE [DoctorName] IS NOT NULL
AND [DoctorName] LIKE '%[^吖-座/\()]%'
AND [DoctorName] LIKE '%[吖-座]%'
SET @i = 0
SET @s2 = ''
WHILE @i <= LEN(@s)
BEGIN
IF SUBSTRING(@s, @i, 1) NOT LIKE '%[^吖-座]%'
SET @s2 = @s2 + SUBSTRING(@s, @i, 1)
SET @i = @i + 1
END
SET @a = @a + 1
SET @e = @e + 1
SET @q = @e / @n
SELECT @s2,
@s,
@n,
CAST(@q AS DECIMAL(5, 4))
UPDATE [MY_DY].[dbo].[table1]
SET DoctorName = @s2
WHERE DoctorName = @s
END