22,300
社区成员




CREATE FUNCTION [dbo].[MY_STR](@S NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9,A-F]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^0-9,A-F]%',@S),1,N'')
RETURN @S
END
---使用
DECLARE @STR NVARCHAR(100)
SET @STR=N'788D4D9729A2D1A3AEBDC3C'
SELECT [dbo].[MY_STR](@STR)
--res
-----------------------------
788D4D9729A2D1A3AEBDC3C
(1 row(s) affected)
if not object_id('tb') is null
drop table tb
Go
Create table tb([col] nvarchar(16))
Insert tb
select N'5B7C6200氂裐E ' union all
select N'20AD7DF90P,Df(8' union all
select N'14B6EC3A甬锎藏兝'
Go
while exists (select 1 from tb where patindex('%[^0-9A-F]%',[col])>0)
begin
update tb set [col]=stuff([col],patindex('%[^0-9A-F]%',[col]),1,'')
where patindex('%[^0-9A-F]%',[col])>0
end
select * from tb
/*
col
----------------
5B7C6200E
20AD7DF90Df8
14B6EC3A
*/