清理varchar列的T-Sql查询

Question

我有一张表中的一个 varchar 列，其中包含以下数据:

1234abc
1234abcde456757
1234abc Supervisor
1234abc456 Administrator

我希望您能够将其清除，即删除紧跟在字母或数字后面的任何字符。对于上述示例，我想要得到以下结果：

1234
1234
1234 Supervisor
1234 Administrator

换句话说，我想保留初始数字和最后一个单词。我正在使用SUBSTRING和CHARINDEX函数，但这些函数会删除直到字符串结尾的所有内容，而我不知道需要删除的部分的长度。

有什么建议吗？

谢谢。

- del.ave

---

初始数字部分始终是4位数吗？ - Ken Keenan

不，我不知道初始部分的长度。 - del.ave

我的基于集合的解决方案比循环更好，可以处理任意数量的数字，以及没有数字、没有单词或多个单词的情况。 - KM.

Answer 1

您可以在子查询中搜索第一个非数字和第一个空格。如果数字的数量不是恰好四个，则该方法也适用：

declare @t table (col1 varchar(50))
insert into @t select '12abc'
union all select '1234abcde456757'
union all select '1234abc Supervisor'
union all select '1234abc456 Administrator'
union all select '123456abc456 Administrator'

select  case when FirstNonDigit = 0 then col1
             when FirstSpace = 0 then substring(col1, 1, FirstNonDigit-1)
             else substring(col1, 1, FirstNonDigit-1) + 
                  substring(col1, FirstSpace, len(col1) - FirstSpace + 1)
             end
from    (
        select  patindex('%[^0-9]%', col1) FirstNonDigit
        ,       patindex('% %', col1) FirstSpace
        ,       col1
        from    @t
        ) subqueryalias

-->

12
1234
1234 Supervisor
1234 Administrator
123456 Administrator

- Andomar

---

谢谢。当值只是一个没有数字或空格的字符串，例如“管理员”时，也可以工作。我不知道表中有这样的值，但显然确实存在。 - del.ave

不适用于像“管理员”（您会得到一个前导空格）或“abc456管理员”（获取完全空白值）这样的值。 - KM.

Answer 2

试试这个：

DECLARE @YourTable table (RowValue varchar(50))
INSERT @YourTable VALUES ('1234abc')
INSERT @YourTable VALUES ('1234abcde456757')
INSERT @YourTable VALUES ('1234abc Supervisor')
INSERT @YourTable VALUES ('1234abc456 Administrator')

UPDATE @YourTable
    SET RowValue=LEFT(RowValue,4)+RIGHT(RowValue,CHARINDEX(' ',REVERSE(RowValue)))
    FROM @YourTable

SELECT * FROM @YourTable

输出：

RowValue
--------------------------------------------------
1234
1234
1234 Supervisor
1234 Administrator

(4 row(s) affected)

编辑：基于集合，可以处理任意数量的数字，也可以处理没有数字或单词的情况。

DECLARE @YourTable table (RowValue varchar(50))
set nocount on
INSERT @YourTable VALUES ('13')
INSERT @YourTable VALUES ('1234abc')
INSERT @YourTable VALUES ('1234abc')
INSERT @YourTable VALUES ('1234abcde456757')
INSERT @YourTable VALUES ('1234abc Supervisor')
INSERT @YourTable VALUES ('1234abc456 Administrator')
INSERT @YourTable VALUES ('1234567abc456 Administrator')
INSERT @YourTable VALUES ('Administrator')
INSERT @YourTable VALUES ('abcde Administrator')

set nocount off

;WITH Digits AS
(SELECT 0 AS Digit UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

)
,Numbers AS
(SELECT 1 AS Number
 UNION ALL
 SELECT Number+1 FROM Numbers where Number<1000
)
,FindDigits AS
(
SELECT
    y.RowValue,n.Number,SUBSTRING(y.RowValue,n.Number,1) AS CharOf,CASE WHEN SUBSTRING(y.RowValue,n.Number,1) LIKE '[0-9]' THEN 'N' ELSE 'A' END AS TypeOf
    FROM @YourTable         y
        INNER JOIN Numbers  n ON 1=1
    WHERE n.Number<=LEN(y.RowValue)
)
,LenOf AS
(
SELECT 
    RowValue,MIN(Number)-1 AS Digits
    FROM FindDigits
    WHERE TypeOf='A'
    GROUP BY RowValue
    HAVING MIN(Number)-1>0
UNION
SELECT 
    f.RowValue,LEN(f.RowValue)
    FROM FindDigits f
    WHERE NOT EXISTS (SELECT 1 FROM FindDigits f2 WHERE f.RowValue=f2.RowValue AND TypeOf='A')
)
UPDATE y
    SET RowValue=CASE WHEN l.Digits IS NOT NULL THEN LEFT(y.RowValue,l.Digits)+RIGHT(y.RowValue,CHARINDEX(' ',REVERSE(y.RowValue)))
                      WHEN CHARINDEX(' ',REVERSE(y.RowValue))=0 THEN y.RowValue
                      ELSE RIGHT(y.RowValue,CHARINDEX(' ',REVERSE(y.RowValue))-1) END
    FROM @YourTable       y
        LEFT JOIN LenOf   l ON y.RowValue=l.RowValue
  OPTION (MAXRECURSION 1000)

SELECT * FROM @YourTable

输出：

RowValue
--------------------------------------------------
13
1234
1234
1234
1234 Supervisor
1234 Administrator
1234567 Administrator
Administrator
Administrator

(9 row(s) affected)

- KM.

---

这假设初始数字的位数为四，不确定是否是这种情况！ - Andomar

Answer 3

你实际上需要两个字符串，一个是从索引0到3的字符，另一个是从空格后面的位置到字符串末尾的字符。我认为这个方法可以实现（但我还没有尝试过）：

UPDATE TableName SET ColumnName = SUBSTRING(ColumnName,1,4) + 
    SUBSTRING(ColumnName,CHARINDEX(' ',ColumnName)+1,LEN(ColumnName))

- Matthew Jones

---

这个不行，CHARINDEX的第一个参数是要查找的字符串而不是要搜索的字符串，你使用了CHARINDEX(ColumnName,' ')，请参阅CHARINDEX (Transact-SQL)，更不用说你要查找第一个空格而不是最后一个了。 - KM.

@KM 修复了 CHARINDEX。根据 OP 的问题，我认为假设数据中只有一个空格是合理的。 - Matthew Jones

Answer 4

下面的代码使用一个值的“计数表”来查找第一个非数字字符和最后一个空格。KM使用PATINDEX的解决方案可能更加优雅！

DECLARE @t TABLE 
(
   c VARCHAR(MAX)
);

INSERT INTO @t VALUES('1234abc');
INSERT INTO @t VALUES('1234abcde456757');
INSERT INTO @t VALUES('1234abc Supervisor');
INSERT INTO @t VALUES('1234abc456 Administrator');

WITH Tally AS
(
   SELECT ROW_NUMBER() OVER (ORDER BY s1.[id]) AS i
   FROM sys.sysobjects s1 CROSS JOIN sys.sysobjects s2 CROSS JOIN sys.sysobjects s3
), 
NumPart AS
(
   SELECT c, MIN(i) AS firstNonNumber
   FROM @t CROSS JOIN Tally
   WHERE i <= LEN(c)
   AND SUBSTRING(c, i, 1) < '0' OR SUBSTRING(c, i, 1) > '9'
   GROUP BY c 
),
SpacePart AS
(
   SELECT c, MAX(i) AS spacePos
   FROM @t t CROSS JOIN Tally
   WHERE i <=  LEN(c)
   AND SUBSTRING(c, i, 1) = ' '
   GROUP BY c
)
UPDATE t
SET t.c = LEFT(n.c, n.firstNonNumber - 1) + 
   CASE WHEN ISNULL(s.SpacePos, 0) > 0 THEN 
      RIGHT(n.c, LEN(n.c) - s.SpacePos + 1) 
   ELSE 
      ''
   END
FROM @t t
INNER JOIN NumPart n ON t.c = n.c
LEFT JOIN SpacePart s ON n.c = s.c;

SELECT * FROM @t;

- Ken Keenan