如何从字符串中删除所有非字母的字符?
非字母数字呢?
这必须是一个自定义函数还是也有更通用的解决方案?
如何从字符串中删除所有非字母的字符?
非字母数字呢?
这必须是一个自定义函数还是也有更通用的解决方案?
当前回答
SQL Server 2017+的另一个可能的选项,没有循环和/或递归,是使用TRANSLATE()和REPLACE()的基于字符串的方法。
t - sql声明:
DECLARE @pattern varchar(52) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT
v.[Text],
REPLACE(
TRANSLATE(
v.[Text],
REPLACE(TRANSLATE(v.[Text], @pattern, REPLICATE('a', LEN(@pattern))), 'a', ''),
REPLICATE('0', LEN(REPLACE(TRANSLATE(v.[Text], @pattern, REPLICATE('a', LEN(@pattern))), 'a', '')))
),
'0',
''
) AS AlphabeticCharacters
FROM (VALUES
('abc1234def5678ghi90jkl#@$&'),
('1234567890'),
('JAHDBESBN%*#*@*($E*sd55bn')
) v ([Text])
或作为一个函数:
CREATE FUNCTION dbo.RemoveNonAlphabeticCharacters (@Text varchar(1000))
RETURNS varchar(1000)
AS BEGIN
DECLARE @pattern varchar(52) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @text = REPLACE(
TRANSLATE(
@Text,
REPLACE(TRANSLATE(@Text, @pattern, REPLICATE('a', LEN(@pattern))), 'a', ''),
REPLICATE('0', LEN(REPLACE(TRANSLATE(@Text, @pattern, REPLICATE('a', LEN(@pattern))), 'a', '')))
),
'0',
''
)
RETURN @Text
END
其他回答
虽然这篇文章有点老了,但我想说以下几点。 我有上述解决方案的问题是,它没有过滤出字符,如ç, ë, ï等。我调整了一个函数如下(我只使用80 varchar字符串来节省内存):
create FUNCTION dbo.udf_Cleanchars (@InputString varchar(80))
RETURNS varchar(80)
AS
BEGIN
declare @return varchar(80) , @length int , @counter int , @cur_char char(1)
SET @return = ''
SET @length = 0
SET @counter = 1
SET @length = LEN(@InputString)
IF @length > 0
BEGIN WHILE @counter <= @length
BEGIN SET @cur_char = SUBSTRING(@InputString, @counter, 1) IF ((ascii(@cur_char) in (32,44,46)) or (ascii(@cur_char) between 48 and 57) or (ascii(@cur_char) between 65 and 90) or (ascii(@cur_char) between 97 and 122))
BEGIN SET @return = @return + @cur_char END
SET @counter = @counter + 1
END END
RETURN @return END
我把它放在调用PatIndex的两个地方。
PatIndex('%[^A-Za-z0-9]%', @Temp)
为上面的自定义函数RemoveNonAlphaCharacters并重命名为RemoveNonAlphaNumericCharacters
这种方式没有为我工作,因为我试图保持阿拉伯字母,我试图取代正则表达式,但它也不起作用。我写了另一个方法工作在ASCII级别,因为这是我唯一的选择,它工作。
Create function [dbo].[RemoveNonAlphaCharacters] (@s varchar(4000)) returns varchar(4000)
with schemabinding
begin
if @s is null
return null
declare @s2 varchar(4000)
set @s2 = ''
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@s, @p, 1))
if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 or @c between 165 and 253 or @c between 32 and 33
set @s2 = @s2 + char(@c)
set @p = @p + 1
end
if len(@s2) = 0
return null
return @s2
end
GO
我刚在Oracle 10g中找到了这个,如果你用的就是它的话。为了进行电话号码比较,我必须去掉所有的特殊字符。
regexp_replace(c.phone, '[^0-9]', '')
CREATE FUNCTION remove_spc_char(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @resp VARCHAR(MAX) = '';
DECLARE @str_val VARCHAR(MAX) = UPPER(@str);
DECLARE @i INTEGER= 1;
DECLARE @v_asc INTEGER;
WHILE @i <= (LEN(@str_val))
BEGIN
SET @v_asc = (ASCII(SUBSTRING(@str_val, @i, 1)))
BEGIN
IF @v_asc in (192,193,194,195,196,65)
begin
SET @v_asc = 65;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (200,201,202,203,233,69)
begin
SET @v_asc = 69;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (204,205,206,207,296,73)
begin
SET @v_asc = 73;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (210,211,212,213,214,79)
begin
SET @v_asc = 79;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (217,218,219,220,85)
begin
SET @v_asc = 85;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (199,231,67)
begin
SET @v_asc = 67;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (209,78)
begin
SET @v_asc = 78;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (924,181,358,216,222,330,272,208,198,42,37,38,34,36,35,
64,33,39,41,40,43,61,95,45,62,60,63,47,176,183,124,166,174,359,248,254,
180,170,186,126,312,331,273,172,178,179,163,162,123,91,93,125,92,167,240,
223,230,171,187,169,185,168)
begin
SET @resp = concat(@resp, '');
end;
ELSE
begin
if @v_asc not in (65,67,69,73,78,79,85)
begin
SET @resp = concat(@resp, CHAR(@v_asc));
end;
end;
END;
SET @i = @i + 1
END;
RETURN @resp;
END;