blocks|key|1312501|text|尝试此函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1312502|Create+Function+[dbo].[RemoveNonAlphaCharacters](@Temp+VarChar(1000))
Returns+VarChar(1000)
AS
Begin

++++Declare+@KeepValues+as+varchar(50)
++++Set+@KeepValues+=+'%25[%5Ea-z]%25'
++++While+PatIndex(@KeepValues,+@Temp)+>+0
++++++++Set+@Temp+=+Stuff(@Temp,+PatIndex(@KeepValues,+@Temp),+1,+'')

++++Return+@Temp
End|code-block|syntax|javascript|1312503|这样叫它：|1312504|Select+dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')|1312505|一旦您理解了代码，您应该会发现更改它以删除其他字符也是相对简单的。您甚至可以使其足够动态，以传入您的搜索模式。|1312506|希望能有所帮助。|1312507|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Try this function:

<pre><code>Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

 Declare @KeepValues as varchar(50)
 Set @KeepValues = '%[^a-z]%'
 While PatIndex(@KeepValues, @Temp) &gt; 0
 Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

 Return @Temp
End
</code></pre>

Call it like this:

<pre><code>Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')
</code></pre>

Once you understand the code, you should see that it is relatively simple to change it to remove other characters, too. You could even make this dynamic enough to pass in your search pattern.

Hope it helps.

blocks|key|1312556|text|George+Mastros的awesome+answer的参数化版本|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1312557|CREATE+FUNCTION+[dbo].[fn_StripCharacters]
(
++++@String+NVARCHAR(MAX),+
++++@MatchExpression+VARCHAR(255)
)
RETURNS+NVARCHAR(MAX)
AS
BEGIN
++++SET+@MatchExpression+=++'%25['%2B@MatchExpression%2B']%25'
++++
++++WHILE+PatIndex(@MatchExpression,+@String)+>+0
++++++++SET+@String+=+Stuff(@String,+PatIndex(@MatchExpression,+@String),+1,+'')
++++
++++RETURN+@String
++++
END|code-block|syntax|javascript|1312558|仅按字母排序：|1312559|SELECT+dbo.fn_StripCharacters('a1!s2@d3#f4$',+'%5Ea-z')|1312560|仅限数字：|1312561|SELECT+dbo.fn_StripCharacters('a1!s2@d3#f4$',+'%5E0-9')|1312562|仅限字母数字：|1312563|SELECT+dbo.fn_StripCharacters('a1!s2@d3#f4$',+'%5Ea-z0-9')|1312564|非字母数字：|1312565|SELECT+dbo.fn_StripCharacters('a1!s2@d3#f4$',+'a-z0-9')|1312566|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/1408129/george-mastros|1|https://stackoverflow.com/questions/1007697/1008566#1008566^0|0|E|0|F|E|1|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]|$A|1C|B|1D|1|1E]]|C|$]]|$1|D|3|E|5|F|7|1F|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|1G|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|1H|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|1I|8|@]|9|@]|C|$]]|$1|O|3|P|5|F|7|1J|8|@]|9|@]|C|$G|H]]|$1|Q|3|R|5|6|7|1K|8|@]|9|@]|C|$]]|$1|S|3|T|5|F|7|1L|8|@]|9|@]|C|$G|H]]|$1|U|3|V|5|6|7|1M|8|@]|9|@]|C|$]]|$1|W|3|X|5|F|7|1N|8|@]|9|@]|C|$G|H]]|$1|Y|3|-4|5|6|7|1O|8|@]|9|@]|C|$]]]|Z|$10|$5|11|12|13|C|$14|15]]|16|$5|11|12|13|C|$14|17]]]]

Parameterized version of <a href="https://stackoverflow.com/users/1408129/george-mastros">George Mastros</a>' <a href="https://stackoverflow.com/questions/1007697/1008566#1008566">awesome answer</a>:
<pre><code>CREATE FUNCTION [dbo].[fn_StripCharacters]
(
 @String NVARCHAR(MAX), 
 @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
 SET @MatchExpression = '%['+@MatchExpression+']%'
 
 WHILE PatIndex(@MatchExpression, @String) &gt; 0
 SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
 
 RETURN @String
 
END
</code></pre>
Alphabetic only:
<pre><code>SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')
</code></pre>
Numeric only:
<pre><code>SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')
</code></pre>
Alphanumeric only:
<pre><code>SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')
</code></pre>
Non-alphanumeric:
<pre><code>SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')
</code></pre>

blocks|key|1312603|text|我把它放在调用PatIndex的两个地方。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1312604|PatIndex('%25[%5EA-Za-z0-9]%25',+@Temp)|code-block|syntax|javascript|1312605|对于上面的RemoveNonAlphaCharacters自定义函数，将其重命名为RemoveNonAlphaNumericCharacters|1312606|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I put this in both places where PatIndex is called.

<pre><code>PatIndex('%[^A-Za-z0-9]%', @Temp)
</code></pre>

for the custom function above RemoveNonAlphaCharacters and renamed it RemoveNonAlphaNumericCharacters

blocks|key|1527734|text|虽然帖子有点老了，但我还是想说以下几点。我在上面的解决方案中遇到的问题是，它不能过滤出ç、ë、as等字符。我调整了一个函数，如下所示(为了节省内存，我只使用了一个80+varchar的字符串)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1527735|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+%2B+@cur_char+END+
SET+@counter+=+@counter+%2B+1+
END+END+

RETURN+@return+END|code-block|syntax|javascript|1527736|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Though post is a bit old, I would like to say the following.
Issue I had with above solution is that it does not filter out characters like ç, ë, ï, etc. I adapted a function as follows (I only used an 80 varchar string to save memory):

<pre><code>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 &gt; 0 
BEGIN WHILE @counter &lt;= @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
</code></pre>

blocks|key|1527882|text|我刚刚发现这个内置在Oracle10g中，如果这就是您正在使用的。为了进行电话号码比较，我不得不去掉所有的特殊字符。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1527883|regexp_replace(c.phone,+'[%5E0-9]',+'')|code-block|syntax|javascript|1527884|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I just found this built into Oracle 10g if that is what you're using. I had to strip all the special characters out for a phone number compare.

<pre><code>regexp_replace(c.phone, '[^0-9]', '')
</code></pre>

blocks|key|1312851|text|使用CTE生成的数字表来检查每个字符，然后让XML连接到保留值的字符串，您可以...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1312852|CREATE+FUNCTION+[dbo].[PatRemove](
++++@pattern+varchar(50),
++++@expression+varchar(8000)+
++++)
RETURNS+varchar(8000)
AS
BEGIN
++++WITH+
++++++++d(d)+AS+(SELECT+d+FROM+(VALUES+(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))+digits(d)),
++++++++nums(n)+AS+(SELECT+ROW_NUMBER()+OVER+(ORDER+BY+(SELECT+NULL))+FROM+d+d1,+d+d2,+d+d3,+d+d4),
++++++++chars(c)+AS+(SELECT+SUBSTRING(@expression,+n,+1)+FROM+nums+WHERE+n+<=+LEN(@expression))
++++SELECT+
++++++++@expression+=+(SELECT+c+AS+[text()]+FROM+chars+WHERE+c+NOT+LIKE+@pattern+FOR+XML+PATH(''));

++++RETURN+@expression;
END|code-block|syntax|javascript|1312853|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Using a CTE generated numbers table to examine each character, then FOR XML to concat to a string of kept values you can...

<pre><code>CREATE FUNCTION [dbo].[PatRemove](
 @pattern varchar(50),
 @expression varchar(8000) 
 )
RETURNS varchar(8000)
AS
BEGIN
 WITH 
 d(d) AS (SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) digits(d)),
 nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM d d1, d d2, d d3, d d4),
 chars(c) AS (SELECT SUBSTRING(@expression, n, 1) FROM nums WHERE n &lt;= LEN(@expression))
 SELECT 
 @expression = (SELECT c AS [text()] FROM chars WHERE c NOT LIKE @pattern FOR XML PATH(''));

 RETURN @expression;
END
</code></pre>

blocks|key|1312898|text|这个解决方案的灵感来自Allen先生的解决方案，它需要一个包含整数的Numbers表(如果您想要以良好的性能执行严肃的查询操作，就应该手头有这个表)。它不需要CTE。您可以更改NOT+IN+(...)表达式以排除特定字符，或将其更改为IN+(...)+or+LIKE表达式以仅保留某些字符。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1312899|SELECT+(
++++SELECT++SUBSTRING([YourString],+N,+1)
++++FROM++++dbo.Numbers
++++WHERE+++N+>+0+AND+N+<=+CONVERT(INT,+LEN([YourString]))
++++++++AND+SUBSTRING([YourString],+N,+1)+NOT+IN+('(',')',',','.')
++++FOR+XML+PATH('')
)+AS+[YourStringTransformed]
FROM+...|code-block|syntax|javascript|1312900|entityMap^0|Y|7|2G|C|39|8|3L|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

This solution, inspired by Mr. Allen's solution, requires a <code>Numbers</code> table of integers (which you should have on hand if you want to do serious query operations with good performance). It does not require a CTE. You can change the <code>NOT IN (...)</code> expression to exclude specific characters, or change it to an <code>IN (...)</code> OR <code>LIKE</code> expression to retain only certain characters.

<pre><code>SELECT (
 SELECT SUBSTRING([YourString], N, 1)
 FROM dbo.Numbers
 WHERE N &gt; 0 AND N &lt;= CONVERT(INT, LEN([YourString]))
 AND SUBSTRING([YourString], N, 1) NOT IN ('(',')',',','.')
 FOR XML PATH('')
) AS [YourStringTransformed]
FROM ...
</code></pre>

blocks|key|1528057|text|看过所有给定的解决方案后，我认为必须有一个纯SQL方法，它不需要函数或CTE+/+XML查询，也不涉及难以维护嵌套的REPLACE语句。以下是我的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1528058|SELECT+
++x
++,CASE+WHEN+a+NOT+LIKE+'%25'+%2B+SUBSTRING(x,+1,+1)+%2B+'%25'+THEN+''+ELSE+SUBSTRING(x,+1,+1)+END
++++%2B+CASE+WHEN+a+NOT+LIKE+'%25'+%2B+SUBSTRING(x,+2,+1)+%2B+'%25'+THEN+''+ELSE+SUBSTRING(x,+2,+1)+END
++++%2B+CASE+WHEN+a+NOT+LIKE+'%25'+%2B+SUBSTRING(x,+3,+1)+%2B+'%25'+THEN+''+ELSE+SUBSTRING(x,+3,+1)+END
++++%2B+CASE+WHEN+a+NOT+LIKE+'%25'+%2B+SUBSTRING(x,+4,+1)+%2B+'%25'+THEN+''+ELSE+SUBSTRING(x,+4,+1)+END
++++%2B+CASE+WHEN+a+NOT+LIKE+'%25'+%2B+SUBSTRING(x,+5,+1)+%2B+'%25'+THEN+''+ELSE+SUBSTRING(x,+5,+1)+END
++++%2B+CASE+WHEN+a+NOT+LIKE+'%25'+%2B+SUBSTRING(x,+6,+1)+%2B+'%25'+THEN+''+ELSE+SUBSTRING(x,+6,+1)+END
--+Keep+adding+rows+until+you+reach+the+column+size+
++++AS+stripped_column
FROM+(SELECT+
++++++++column_to_strip+AS+x
++++++++,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'+AS+a+
++++++FROM+my_table)+a|code-block|syntax|javascript|1528059|这样做的好处是，有效字符包含在子查询中的一个字符串中，因此很容易为不同的字符集重新配置。|1528060|缺点是您必须为每个字符添加一行SQL，直到您的列的大小。为了简化这项任务，我只使用了下面的Powershell脚本，对于VARCHAR(64)，此示例如下：|1528061|1..64+%7C+%25+{
++"++++%2B+CASE+WHEN+a+NOT+LIKE+'%25'+%2B+SUBSTRING(x,+{0},+1)+%2B+'%25'+THEN+''+ELSE+SUBSTRING(x,+{0},+1)+END"+-f+$_
}+%7C+clip.exe|1528062|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Having looked at all the given solutions I thought that there has to be a pure SQL method that does not require a function or a CTE / XML query, and doesn't involve difficult to maintain nested REPLACE statements. Here is my solution:

<pre><code>SELECT 
 x
 ,CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 1, 1) + '%' THEN '' ELSE SUBSTRING(x, 1, 1) END
 + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 2, 1) + '%' THEN '' ELSE SUBSTRING(x, 2, 1) END
 + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 3, 1) + '%' THEN '' ELSE SUBSTRING(x, 3, 1) END
 + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 4, 1) + '%' THEN '' ELSE SUBSTRING(x, 4, 1) END
 + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 5, 1) + '%' THEN '' ELSE SUBSTRING(x, 5, 1) END
 + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 6, 1) + '%' THEN '' ELSE SUBSTRING(x, 6, 1) END
-- Keep adding rows until you reach the column size 
 AS stripped_column
FROM (SELECT 
 column_to_strip AS x
 ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS a 
 FROM my_table) a
</code></pre>

The advantage of doing it this way is that the valid characters are contained in the one string in the sub query making easy to reconfigure for a different set of characters.

The downside is that you have to add a row of SQL for each character up to the size of your column. To make that task easier I just used the Powershell script below, this example if for a VARCHAR(64):

<pre><code>1..64 | % {
 " + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, {0}, 1) + '%' THEN '' ELSE SUBSTRING(x, {0}, 1) END" -f $_
} | clip.exe
</code></pre>

blocks|key|1528135|text|这里有一个解决方案，它不需要创建函数或列出要替换的所有字符实例。它结合使用递归WITH语句和PATINDEX来查找不需要的字符。它将替换一列中所有不需要的字符-任何给定字符串中最多包含100个唯一的错误字符。(例如，"ABC123DEF234“将包含4个错误字符1、2、3和4)+100限制是WITH语句中允许的最大递归数量，但这不会对要处理的行数施加限制，这只受可用内存的限制。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1528136|如果您不想要截然不同的结果，可以从代码中删除这两个选项。|1528137|--+Create+some+test+data:
SELECT+*+INTO+#testData+
FROM+(VALUES+('ABC+DEF,K.l(p)'),('123H,J,234'),('ABCD+EFG'))+as+t(TXT)

--+Actual+query:
--+Remove+non-alpha+chars:+'%25[%5EA-Z]%25'
--+Remove+non-alphanumeric+chars:+'%25[%5EA-Z0-9]%25'
DECLARE+@BadCharacterPattern+VARCHAR(250)+=+'%25[%5EA-Z]%25';

WITH+recurMain+as+(
++++SELECT+DISTINCT+CAST(TXT+AS+VARCHAR(250))+AS+TXT,+PATINDEX(@BadCharacterPattern,+TXT)+AS+BadCharIndex
++++FROM+#testData
++++UNION+ALL
++++SELECT+CAST(TXT+AS+VARCHAR(250))+AS+TXT,+PATINDEX(@BadCharacterPattern,+TXT)+AS+BadCharIndex
++++FROM+(
++++++++SELECT+
++++++++++++CASE+WHEN+BadCharIndex+>+0+
++++++++++++++++THEN+REPLACE(TXT,+SUBSTRING(TXT,+BadCharIndex,+1),+'')
++++++++++++++++ELSE+TXT+
++++++++++++END+AS+TXT
++++++++FROM+recurMain
++++++++WHERE+BadCharIndex+>+0
++++)+badCharFinder
)
SELECT+DISTINCT+TXT
FROM+recurMain
WHERE+BadCharIndex+=+0;|code-block|syntax|javascript|1528138|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here's a solution that doesn't require creating a function or listing all instances of characters to replace. It uses a recursive WITH statement in combination with a PATINDEX to find unwanted chars. It will replace all unwanted chars in a column - up to 100 unique bad characters contained in any given string. (E.G. "ABC123DEF234" would contain 4 bad characters 1, 2, 3 and 4) The 100 limit is the maximum number of recursions allowed in a WITH statement, but this doesn't impose a limit on the number of rows to process, which is only limited by the memory available. 
If you don't want DISTINCT results, you can remove the two options from the code.

<pre><code>-- Create some test data:
SELECT * INTO #testData 
FROM (VALUES ('ABC DEF,K.l(p)'),('123H,J,234'),('ABCD EFG')) as t(TXT)

-- Actual query:
-- Remove non-alpha chars: '%[^A-Z]%'
-- Remove non-alphanumeric chars: '%[^A-Z0-9]%'
DECLARE @BadCharacterPattern VARCHAR(250) = '%[^A-Z]%';

WITH recurMain as (
 SELECT DISTINCT CAST(TXT AS VARCHAR(250)) AS TXT, PATINDEX(@BadCharacterPattern, TXT) AS BadCharIndex
 FROM #testData
 UNION ALL
 SELECT CAST(TXT AS VARCHAR(250)) AS TXT, PATINDEX(@BadCharacterPattern, TXT) AS BadCharIndex
 FROM (
 SELECT 
 CASE WHEN BadCharIndex &gt; 0 
 THEN REPLACE(TXT, SUBSTRING(TXT, BadCharIndex, 1), '')
 ELSE TXT 
 END AS TXT
 FROM recurMain
 WHERE BadCharIndex &gt; 0
 ) badCharFinder
)
SELECT DISTINCT TXT
FROM recurMain
WHERE BadCharIndex = 0;
</code></pre>

blocks|key|1528196|text|--首先创建一个函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1528197|CREATE+FUNCTION+[dbo].[GetNumericonly]
(@strAlphaNumeric+VARCHAR(256))
RETURNS+VARCHAR(256)
AS
BEGIN
+++++DECLARE+@intAlpha+INT
+++++SET+@intAlpha+=+PATINDEX('%25[%5E0-9]%25',+@strAlphaNumeric)
BEGIN
+++++WHILE+@intAlpha+>+0
+++BEGIN
++++++++++SET+@strAlphaNumeric+=+STUFF(@strAlphaNumeric,+@intAlpha,+1,+''+)
++++++++++SET+@intAlpha+=+PATINDEX('%25[%5E0-9]%25',+@strAlphaNumeric+)
+++END
END
RETURN+ISNULL(@strAlphaNumeric,0)
END|code-block|syntax|javascript|1528198|现在调用这个函数，如下所示|1528199|select+[dbo].[GetNumericonly]('Abhi12shek23jaiswal')|1528200|它的结果如下|1528201|1223|1528202|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

--First create one function

<pre><code>CREATE FUNCTION [dbo].[GetNumericonly]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
 DECLARE @intAlpha INT
 SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
 WHILE @intAlpha &gt; 0
 BEGIN
 SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
 SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
 END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
</code></pre>

Now call this function like

<pre><code>select [dbo].[GetNumericonly]('Abhi12shek23jaiswal')
</code></pre>

Its result like

<pre><code>1223
</code></pre>

blocks|key|1313103|text|DECLARE+@vchVAlue+NVARCHAR(255)+=+'SWP,+Lettering+Position+1:+4+Ω,+2:+8+Ω,+3:+16+Ω,+4:++,+5:++,+6:++,+Voltage+Selector,+Solder,+6,+Step+switch,+:+w/o+fuseholder+'


WHILE+PATINDEX('%25?%25'+,+CAST(@vchVAlue+AS+VARCHAR(255)))+>+0
++BEGIN
++++SELECT+@vchVAlue+=+STUFF(@vchVAlue,PATINDEX('%25?%25'+,+CAST(@vchVAlue+AS+VARCHAR(255))),1,'+')
++END+

SELECT+@vchVAlue|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1313104|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DECLARE @vchVAlue NVARCHAR(255) = 'SWP, Lettering Position 1: 4 Ω, 2: 8 Ω, 3: 16 Ω, 4: , 5: , 6: , Voltage Selector, Solder, 6, Step switch, : w/o fuseholder '


WHILE PATINDEX('%?%' , CAST(@vchVAlue AS VARCHAR(255))) &gt; 0
 BEGIN
 SELECT @vchVAlue = STUFF(@vchVAlue,PATINDEX('%?%' , CAST(@vchVAlue AS VARCHAR(255))),1,' ')
 END 

SELECT @vchVAlue
</code></pre>

blocks|key|1313210|text|这种方法对我不起作用，因为我试图保留阿拉伯字母，我试图替换正则表达式，但它也不起作用。我写了另一个方法在ASCII级别上工作，因为这是我唯一的选择，并且它起作用了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1313211|+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+%2B+char(@c)
++++++set+@p+=+@p+%2B+1
++++++end
+++if+len(@s2)+=+0
++++++return+null
+++return+@s2
+++end|code-block|syntax|javascript|1313212|去|1313213|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

this way didn't work for me as i was trying to keep the Arabic letters i tried to replace the regular expression but also it didn't work. i wrote another method to work on ASCII level as it was my only choice and it worked.

<pre><code> 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 &lt;= @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
</code></pre>

GO

blocks|key|1313289|text|信不信由你，在我的系统中，这个丑陋的函数比G+Mastros优雅的函数表现得更好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1313290|CREATE+FUNCTION+dbo.RemoveSpecialChar+(@s+VARCHAR(256))+
RETURNS+VARCHAR(256)+
WITH+SCHEMABINDING
++++BEGIN
++++++++IF+@s+IS+NULL
++++++++++++RETURN+NULL
++++++++DECLARE+@s2+VARCHAR(256)+=+'',
++++++++++++++++@l+INT+=+LEN(@s),
++++++++++++++++@p+INT+=+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
++++++++++++++++++++SET+@s2+=+@s2+%2B+CHAR(@c)
++++++++++++++++SET+@p+=+@p+%2B+1
++++++++++++END

++++++++IF+LEN(@s2)+=+0
++++++++++++RETURN+NULL

++++++++RETURN+@s2|code-block|syntax|javascript|1313291|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Believe it or not, in my system this ugly function performs better than G Mastros elegant one.

<pre><code>CREATE FUNCTION dbo.RemoveSpecialChar (@s VARCHAR(256)) 
RETURNS VARCHAR(256) 
WITH SCHEMABINDING
 BEGIN
 IF @s IS NULL
 RETURN NULL
 DECLARE @s2 VARCHAR(256) = '',
 @l INT = LEN(@s),
 @p INT = 1

 WHILE @p &lt;= @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
 SET @s2 = @s2 + CHAR(@c)
 SET @p = @p + 1
 END

 IF LEN(@s2) = 0
 RETURN NULL

 RETURN @s2
</code></pre>

blocks|key|1528426|text|从性能的角度来看，我会使用内联函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1528427|SET+ANSI_NULLS+ON
GO
SET+QUOTED_IDENTIFIER+ON
GO
CREATE+FUNCTION+[dbo].[udf_RemoveNumericCharsFromString]
(
@List+NVARCHAR(4000)
)
RETURNS+TABLE+
AS+RETURN

++++WITH+GetNums+AS+(
+++++++SELECT+TOP(ISNULL(DATALENGTH(@List),+0))
++++++++n+=+ROW_NUMBER()+OVER(ORDER+BY+(SELECT+NULL))
++++++++FROM
++++++++++(VALUES+(0),(0),(0),(0))+d+(n),
++++++++++(VALUES+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))+e+(n),
++++++++++(VALUES+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))+f+(n),
++++++++++(VALUES+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))+g+(n)
++++++++++++)

++++SELECT+StrOut+=+''%2B
++++++++(SELECT+Chr
+++++++++FROM+GetNums
++++++++++++CROSS+APPLY+(SELECT+SUBSTRING(@List+,+n,1))+X(Chr)
+++++++++WHERE+Chr+LIKE+'%25[%5E0-9]%25'+
+++++++++ORDER+BY+N
+++++++++FOR+XML+PATH+(''),TYPE).value('.','NVARCHAR(MAX)')


+++/*How+to+Use
+++SELECT+StrOut+FROM+dbo.udf_RemoveNumericCharsFromString+('vv45--9gut')
+++Result:+vv--gut
+++*/|code-block|syntax|javascript|1528428|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

From performance perspective I'd use Inline Function:

<pre><code>SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_RemoveNumericCharsFromString]
(
@List NVARCHAR(4000)
)
RETURNS TABLE 
AS RETURN

 WITH GetNums AS (
 SELECT TOP(ISNULL(DATALENGTH(@List), 0))
 n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
 FROM
 (VALUES (0),(0),(0),(0)) d (n),
 (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
 (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
 (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)
 )

 SELECT StrOut = ''+
 (SELECT Chr
 FROM GetNums
 CROSS APPLY (SELECT SUBSTRING(@List , n,1)) X(Chr)
 WHERE Chr LIKE '%[^0-9]%' 
 ORDER BY N
 FOR XML PATH (''),TYPE).value('.','NVARCHAR(MAX)')


 /*How to Use
 SELECT StrOut FROM dbo.udf_RemoveNumericCharsFromString ('vv45--9gut')
 Result: vv--gut
 */
</code></pre>

blocks|key|1313356|text|这是另一个递归的CTE解决方案，基于@Gerhard+Weiss的答案here。您应该能够将整个代码块复制并粘贴到SSMS中，并在那里使用它。结果包括一些额外的专栏，以帮助我们了解发生了什么。我花了一段时间才理解了PATINDEX+(RegEx)和递归CTE的全部内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1313357|DECLARE+@DefineBadCharPattern+varchar(30)
SET+@DefineBadCharPattern+=+'%25[%5EA-z]%25'++--Means+anything+NOT+between+A+and+z+characters+(according+to+ascii+char+value)+is+"bad"
SET+@DefineBadCharPattern+=+'%25[%5Ea-z0-9]%25'++--Means+anything+NOT+between+a+and+z+characters+or+numbers+0+through+9+(according+to+ascii+char+value)+are+"bad"
SET+@DefineBadCharPattern+=+'%25[%5E+-~]%25'++--Means+anything+NOT+between+space+and+~+characters+(all+non-printable+characters)+is+"bad"
--Change+@ReplaceBadCharWith+to+''+to+strip+"bad"+characters+from+string
--Change+to+some+character+if+you+want+to+'see'+what's+being+replaced.+NOTE:+It+must+be+allowed+accoring+to+@DefineBadCharPattern+above
DECLARE+@ReplaceBadCharWith+varchar(1)+=+'#'++--Change+this+to+whatever+you+want+to+replace+non-printable+chars+with+
IF+patindex(@DefineBadCharPattern+COLLATE+Latin1_General_BIN,+@ReplaceBadCharWith)+>+0
++++BEGIN
++++++++RAISERROR('@ReplaceBadCharWith+value+(%25s)+must+be+a+character+allowed+by+PATINDEX+pattern+of+%25s',16,1,@ReplaceBadCharWith,+@DefineBadCharPattern)
++++++++RETURN
++++END
--A+table+of+values+to+play+with:
DECLARE+@temp+TABLE+(OriginalString+varchar(100))
INSERT+@temp+SELECT+'+1hello'+%2B+char(13)+%2B+char(10)+%2B+'there'+%2B+char(30)+%2B+char(9)+%2B+char(13)+%2B+char(10)
INSERT+@temp+SELECT+'2hello'+%2B+char(30)+%2B+'there'+%2B+char(30)
INSERT+@temp+SELECT+'+3hello+there'
INSERT+@temp+SELECT+'+tab'+%2B+char(9)+%2B+'+character'
INSERT+@temp+SELECT+'good+bye'

--Let+the+magic+begin:
;WITH+recurse+AS+(
++++select
++++OriginalString,
++++OriginalString+as+CleanString,
++++patindex(@DefineBadCharPattern+COLLATE+Latin1_General_BIN,+OriginalString)+as+[Position],
++++substring(OriginalString,patindex(@DefineBadCharPattern+COLLATE+Latin1_General_BIN,+OriginalString),1)+as+[InvalidCharacter],
++++ascii(substring(OriginalString,patindex(@DefineBadCharPattern+COLLATE+Latin1_General_BIN,+OriginalString),1))+as+[ASCIICode]
++++from+@temp
+++UNION+ALL
++++select
++++OriginalString,
++++CONVERT(varchar(100),REPLACE(CleanString,InvalidCharacter,@ReplaceBadCharWith)),
++++patindex(@DefineBadCharPattern+COLLATE+Latin1_General_BIN,CleanString)+as+[Position],
++++substring(CleanString,patindex(@DefineBadCharPattern+COLLATE+Latin1_General_BIN,CleanString),1),
++++ascii(substring(CleanString,patindex(@DefineBadCharPattern+COLLATE+Latin1_General_BIN,CleanString),1))
++++from+recurse
++++where+patindex(@DefineBadCharPattern+COLLATE+Latin1_General_BIN,CleanString)+>+0
)
SELECT+*+FROM+recurse
--optionally+comment+out+this+last+WHERE+clause+to+see+more+of+what+the+recursion+is+doing:
WHERE+patindex(@DefineBadCharPattern+COLLATE+Latin1_General_BIN,CleanString)+=+0|code-block|syntax|javascript|1313358|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/3917698/450750^0|Z|4|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Here's another recursive CTE solution, based on @Gerhard Weiss's answer <a href="https://stackoverflow.com/a/3917698/450750">here</a>. You should be able to copy and paste the whole code block into SSMS and play with it there. The results include a few extra columns to help us understand what's going on. It took me a while until I understood all that's going on with both PATINDEX (RegEx) and the recursive CTE.

<pre><code>DECLARE @DefineBadCharPattern varchar(30)
SET @DefineBadCharPattern = '%[^A-z]%' --Means anything NOT between A and z characters (according to ascii char value) is "bad"
SET @DefineBadCharPattern = '%[^a-z0-9]%' --Means anything NOT between a and z characters or numbers 0 through 9 (according to ascii char value) are "bad"
SET @DefineBadCharPattern = '%[^ -~]%' --Means anything NOT between space and ~ characters (all non-printable characters) is "bad"
--Change @ReplaceBadCharWith to '' to strip "bad" characters from string
--Change to some character if you want to 'see' what's being replaced. NOTE: It must be allowed accoring to @DefineBadCharPattern above
DECLARE @ReplaceBadCharWith varchar(1) = '#' --Change this to whatever you want to replace non-printable chars with 
IF patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, @ReplaceBadCharWith) &gt; 0
 BEGIN
 RAISERROR('@ReplaceBadCharWith value (%s) must be a character allowed by PATINDEX pattern of %s',16,1,@ReplaceBadCharWith, @DefineBadCharPattern)
 RETURN
 END
--A table of values to play with:
DECLARE @temp TABLE (OriginalString varchar(100))
INSERT @temp SELECT ' 1hello' + char(13) + char(10) + 'there' + char(30) + char(9) + char(13) + char(10)
INSERT @temp SELECT '2hello' + char(30) + 'there' + char(30)
INSERT @temp SELECT ' 3hello there'
INSERT @temp SELECT ' tab' + char(9) + ' character'
INSERT @temp SELECT 'good bye'

--Let the magic begin:
;WITH recurse AS (
 select
 OriginalString,
 OriginalString as CleanString,
 patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, OriginalString) as [Position],
 substring(OriginalString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, OriginalString),1) as [InvalidCharacter],
 ascii(substring(OriginalString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, OriginalString),1)) as [ASCIICode]
 from @temp
 UNION ALL
 select
 OriginalString,
 CONVERT(varchar(100),REPLACE(CleanString,InvalidCharacter,@ReplaceBadCharWith)),
 patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString) as [Position],
 substring(CleanString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString),1),
 ascii(substring(CleanString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString),1))
 from recurse
 where patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString) &gt; 0
)
SELECT * FROM recurse
--optionally comment out this last WHERE clause to see more of what the recursion is doing:
WHERE patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString) = 0
</code></pre>

blocks|key|1313397|text|SQL+Server+2017%2B的另一个可能的选择是使用TRANSLATE()和REPLACE()的基于字符串的方法，它没有循环和/或递归。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1313398|T-SQL语句：|1313399|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%25*#*@*($E*sd55bn')
)+v+([Text])|code-block|syntax|javascript|1313400|或者作为一个函数：|1313401|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|1313402|entityMap^0|S|B|14|9|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|10|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|R|$]]

Another possibe option for SQL Server 2017+, without loops and/or recursion, is a string-based approach using <code>TRANSLATE()</code> and <code>REPLACE()</code>.
T-SQL statement:
<pre><code>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#@$&amp;'),
 ('1234567890'),
 ('JAHDBESBN%*#*@*($E*sd55bn')
) v ([Text])
</code></pre>
or as a function:
<pre><code>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
</code></pre>

blocks|key|1528577|text|对于SQL+Server+>=+2017...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1528578|declare+@text+varchar(max)

--+create+some+sample+text
select
@text=
'
Lorem+@ipsum++*&dolor-=+sit?!+amet,+{consectetur+}+adipiscing\+elit.+Vivamus+commodo+justo+metus,+sed+facilisis+ante+
congue+eget.+Proin+ac+bibendum+sem/.
'

--+the+characters+to+be+removed
declare+@unwanted+varchar(max)='''.,!?/<>"[]{}%7C`~@#$%25%5E&*()-%2B=/\:;'%2Bchar(13)%2Bchar(10)

--+interim+replaced+with
declare+@replace_with+char(1)='+'

--+call+the+translate+function+that+will+change+unwanted+characters+to+spaces
--+in+this+sample
declare+@translated+varchar(max)
select+@translated=TRANSLATE(@text,@unwanted,REPLICATE(@replace_with,len(@unwanted)))

--+In+this+case,+I+want+to+preserve+one+space
select++string_agg(trim(value),'+')
from++++STRING_SPLIT(@translated,'+')
where+++trim(value)<>''

--+Result
'Lorem+ipsum+dolor+sit+amet+consectetur+adipiscing+elit+Vivamus+commodo+justo+metus+sed+facilisis+ante+congue+eget+Proin+ac+bibendum+sem'|code-block|syntax|javascript|1528579|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For SQL Server &gt;= 2017...
<pre><code>declare @text varchar(max)

-- create some sample text
select
@text=
'
Lorem @ipsum *&amp;dolor-= sit?! amet, {consectetur } adipiscing\ elit. Vivamus commodo justo metus, sed facilisis ante 
congue eget. Proin ac bibendum sem/.
'

-- the characters to be removed
declare @unwanted varchar(max)='''.,!?/&lt;&gt;&quot;[]{}|`~@#$%^&amp;*()-+=/\:;'+char(13)+char(10)

-- interim replaced with
declare @replace_with char(1)=' '

-- call the translate function that will change unwanted characters to spaces
-- in this sample
declare @translated varchar(max)
select @translated=TRANSLATE(@text,@unwanted,REPLICATE(@replace_with,len(@unwanted)))

-- In this case, I want to preserve one space
select string_agg(trim(value),' ')
from STRING_SPLIT(@translated,' ')
where trim(value)&lt;&gt;''

-- Result
'Lorem ipsum dolor sit amet consectetur adipiscing elit Vivamus commodo justo metus sed facilisis ante congue eget Proin ac bibendum sem'
</code></pre>

How could you remove all characters that are not alphabetic from a string? 

What about non-alphanumeric?

Does this have to be a custom function or are there also more generalizable solutions?

How to strip all non-alphabetic characters from string in SQL Server?

SQL Server

如何从字符串中删除所有不是字母的字符？非字母数字呢？这是否必须是一个自定义函数，或者是否也有更通用的解决方案？

问如何从SQL Server中的字符串中剥离所有非字母字符？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从SQL Server中的字符串中剥离所有非字母字符？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从SQL Server中的字符串中剥离所有非字母字符？
EN