blocks|key|29967|text|有关在SQL+Server中从字符串中提取数字的信息，请参阅此blog+post。下面是在您的示例中使用字符串的示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|29968|DECLARE+@textval+NVARCHAR(30)
SET+@textval+=+'AB+ABCDE+#+123'

SELECT+LEFT(SUBSTRING(@textval,+PATINDEX('%25[0-9.-]%25',+@textval),+8000),
+++++++++++PATINDEX('%25[%5E0-9.-]%25',+SUBSTRING(@textval,+PATINDEX('%25[0-9.-]%25',+@textval),+8000)+%2B+'X')+-1)|code-block|syntax|javascript|29969|entityMap|0|LINK|mutability|MUTABLE|url|http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server^0|V|9|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]]]]

See this <a href="http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server" rel="noreferrer">blog post</a> on extracting numbers from strings in SQL Server. Below is a sample using a string in your example:

<pre><code>DECLARE @textval NVARCHAR(30)
SET @textval = 'AB ABCDE # 123'

SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),
 PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)
</code></pre>

blocks|key|246161|text|您可以使用stuff和patindex。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|246162|stuff(Col,+1,+patindex('%25[0-9]%25',+Col)-1,+'')|code-block|syntax|javascript|246163|SQL+Fiddle|246164|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/ms188043.aspx|1|http://technet.microsoft.com/en-us/library/ms188395.aspx|2|http://sqlfiddle.com/#!3/e6ea6/1^0|5|5|0|B|8|1|0|0|0|A|2|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]|$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|F|7|13|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|K|3|-4|5|6|7|18|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]|S|$5|N|O|P|C|$Q|T]]|U|$5|N|O|P|C|$Q|V]]]]

You can use <a href="http://technet.microsoft.com/en-us/library/ms188043.aspx" rel="noreferrer">stuff</a> and <a href="http://technet.microsoft.com/en-us/library/ms188395.aspx" rel="noreferrer">patindex</a>.

<pre class="lang-sql prettyprint-override"><code>stuff(Col, 1, patindex('%[0-9]%', Col)-1, '')
</code></pre>

<a href="http://sqlfiddle.com/#!3/e6ea6/1" rel="noreferrer">SQL Fiddle</a>

blocks|key|29991|text|如果数字之间可能存在某些字符(例如，千位分隔符)，您可以尝试以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|29992|declare+@table+table+(DirtyCol+varchar(100))
insert+into+@table+values
++++('AB+ABCDE+#+123')
++++,('ABCDE#+123')
++++,('AB:+ABC#+123')
++++,('AB#')
++++,('AB+#+1+000+000')
++++,('AB+#+1`234`567')
++++,('AB+#+(9)(876)(543)')

;with+tally+as+(select+top+(100)+N=row_number()+over+(order+by+@@spid)+from+sys.all_columns),
data+as+(
++++select+DirtyCol,+Col
++++from+@table
++++++++cross+apply+(
++++++++++++select+(select+C+%2B+''
++++++++++++from+(select+N,+substring(DirtyCol,+N,+1)+C+from+tally+where+N<=datalength(DirtyCol))+[1]
++++++++++++where+C+between+'0'+and+'9'
++++++++++++order+by+N
++++++++++++for+xml+path(''))
++++++++)+p+(Col)
++++where+p.Col+is+not+NULL
)
select+DirtyCol,+cast(Col+as+int)+IntCol
from+data|code-block|syntax|javascript|29993|输出为：|29994|DirtyCol++++++++++++++IntCol
---------------------+-------
AB+ABCDE+#+123++++++++123
ABCDE#+123++++++++++++123
AB:+ABC#+123++++++++++123
AB+#+1+000+000++++++++1000000
AB+#+1`234`567++++++++1234567
AB+#+(9)(876)(543)++++9876543|29995|对于更新，将ColToUpdate添加到data+cte的选择列表中：|offset|length|style|CODE|29996|;with+num+as+(...),
data+as+(
++++select+ColToUpdate,+/*DirtyCol,+*/Col
++++from+...
)
update+data
set+ColToUpdate+=+cast(Col+as+int)|29997|entityMap^0|0|0|0|0|6|B|K|4|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@$M|Z|N|10|O|P]|$M|11|N|12|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

In case if there are some characters possible between digits (e.g. thousands separators), you may try following:

<pre><code>declare @table table (DirtyCol varchar(100))
insert into @table values
 ('AB ABCDE # 123')
 ,('ABCDE# 123')
 ,('AB: ABC# 123')
 ,('AB#')
 ,('AB # 1 000 000')
 ,('AB # 1`234`567')
 ,('AB # (9)(876)(543)')

;with tally as (select top (100) N=row_number() over (order by @@spid) from sys.all_columns),
data as (
 select DirtyCol, Col
 from @table
 cross apply (
 select (select C + ''
 from (select N, substring(DirtyCol, N, 1) C from tally where N&lt;=datalength(DirtyCol)) [1]
 where C between '0' and '9'
 order by N
 for xml path(''))
 ) p (Col)
 where p.Col is not NULL
)
select DirtyCol, cast(Col as int) IntCol
from data
</code></pre>

Output is:

<pre><code>DirtyCol IntCol
--------------------- -------
AB ABCDE # 123 123
ABCDE# 123 123
AB: ABC# 123 123
AB # 1 000 000 1000000
AB # 1`234`567 1234567
AB # (9)(876)(543) 9876543
</code></pre>

For update, add <code>ColToUpdate</code> to select list of the <code>data</code> cte:

<pre><code>;with num as (...),
data as (
 select ColToUpdate, /*DirtyCol, */Col
 from ...
)
update data
set ColToUpdate = cast(Col as int)
</code></pre>

blocks|key|246188|text|Create+function+fn_GetNumbersOnly(@pn+varchar(100))
++++Returns+varchar(max)
++++AS
++++BEGIN
++++++Declare+@r+varchar(max)+='',+@len+int+,@c+char(1),+@x+int+=+0
++++++Select+@len+=+len(@pn)
++++++while+@x+<=+@len+
++++++begin
++++++++Select+@c+=+SUBSTRING(@pn,@x,1)
++++++++if+ISNUMERIC(@c)+=+1+and+@c+<>+'-'
+++++++++Select+@r+=+@r+%2B+@c
+++++++Select+@x+=+@x+%2B1
++++++end
++++return+@r
End|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|246189|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>Create function fn_GetNumbersOnly(@pn varchar(100))
 Returns varchar(max)
 AS
 BEGIN
 Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0
 Select @len = len(@pn)
 while @x &lt;= @len 
 begin
 Select @c = SUBSTRING(@pn,@x,1)
 if ISNUMERIC(@c) = 1 and @c &lt;&gt; '-'
 Select @r = @r + @c
 Select @x = @x +1
 end
 return @r
End
</code></pre>

blocks|key|246196|text|+Declare+@MainTable+table(id+int+identity(1,1),TextField+varchar(100))
++INSERT+INTO+@MainTable+(TextField)
+VALUES
+('6B32E')
+declare+@i+int=1
++Declare+@originalWord+varchar(100)=''
++WHile+@i<=(Select+count(*)+from+@MainTable)
++BEGIN
++Select+@originalWord=TextField+from+@MainTable+where+id=@i

+Declare+@r+varchar(max)+='',+@len+int+,@c+char(1),+@x+int+=+0

++++Select+@len+=+len(@originalWord)
++++declare+@pn+varchar(100)=@originalWord
++++while+@x+<=+@len+
++++begin

++++++Select+@c+=+SUBSTRING(@pn,@x,1)
++++if(@c!='')
++++BEGIN
++++++++++++if+ISNUMERIC(@c)+=+0+and+@c+<>+'-'
++++BEGIN
+++++Select+@r+=+cast(@r+as+varchar)+%2B+cast(replace((SELECT+ASCII(@c)-64),'-','')+as+varchar)

+++end
+++ELSE
+++BEGIN
++++Select+@r+=+@r+%2B+@c


+++END

END


++++Select+@x+=+@x+%2B1

++++END
++++Select+@r
++Set+@i=@i%2B1
++END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|246197|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 @MainTable table(id int identity(1,1),TextField varchar(100))
 INSERT INTO @MainTable (TextField)
 VALUES
 ('6B32E')
 declare @i int=1
 Declare @originalWord varchar(100)=''
 WHile @i&lt;=(Select count(*) from @MainTable)
 BEGIN
 Select @originalWord=TextField from @MainTable where id=@i

 Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0

 Select @len = len(@originalWord)
 declare @pn varchar(100)=@originalWord
 while @x &lt;= @len 
 begin

 Select @c = SUBSTRING(@pn,@x,1)
 if(@c!='')
 BEGIN
 if ISNUMERIC(@c) = 0 and @c &lt;&gt; '-'
 BEGIN
 Select @r = cast(@r as varchar) + cast(replace((SELECT ASCII(@c)-64),'-','') as varchar)

 end
 ELSE
 BEGIN
 Select @r = @r + @c


 END

END


 Select @x = @x +1

 END
 Select @r
 Set @i=@i+1
 END
</code></pre>

blocks|key|30048|text|这对我来说很好用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|30049|CREATE+FUNCTION+[dbo].[StripNonNumerics]
(
++@Temp+varchar(255)
)
RETURNS+varchar(255)
AS
Begin

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

++++Return+@Temp
End|code-block|syntax|javascript|30050|然后像这样调用函数来查看经过清理的内容旁边的原始内容：|30051|SELECT+Something,+dbo.StripNonNumerics(Something)+FROM+TableA|30052|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This works well for me:

<pre><code>CREATE FUNCTION [dbo].[StripNonNumerics]
(
 @Temp varchar(255)
)
RETURNS varchar(255)
AS
Begin

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

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

Then call the function like so to see the original something next to the sanitized something:

<pre><code>SELECT Something, dbo.StripNonNumerics(Something) FROM TableA
</code></pre>

blocks|key|246250|text|要添加到Ken's+answer，这将处理逗号、空格和圆括号|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|246251|--Handles+parentheses,+commas,+spaces,+hyphens..
declare+@table+table+(c+varchar(256))
insert+into+@table
values
('This+is+a+test+111-222-3344'),
('Some+Sample+Text+(111)-222-3344'),
('Hello+there+111222+3344+/+How+are+you?'),
('Hello+there+111+222+3344+?+How+are+you?'),
('Hello+there+111+222+3344.+How+are+you?')

select
replace(LEFT(SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),'+',''),',',''),+PATINDEX('%25[0-9.-]%25',+replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),'+',''),',','')),+8000),
+++++++++++PATINDEX('%25[%5E0-9.-]%25',+SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),'+',''),',',''),+PATINDEX('%25[0-9.-]%25',+replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),'+',''),',','')),+8000)+%2B+'X')+-1),'.','')
from+@table|code-block|syntax|javascript|246252|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/1102784/ken-richards^0|4|5|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]]]]

To add on to <a href="https://stackoverflow.com/users/1102784/ken-richards">Ken's</a> answer, this handles commas and spaces and parentheses

<pre><code>--Handles parentheses, commas, spaces, hyphens..
declare @table table (c varchar(256))
insert into @table
values
('This is a test 111-222-3344'),
('Some Sample Text (111)-222-3344'),
('Hello there 111222 3344 / How are you?'),
('Hello there 111 222 3344 ? How are you?'),
('Hello there 111 222 3344. How are you?')

select
replace(LEFT(SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000),
 PATINDEX('%[^0-9.-]%', SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000) + 'X') -1),'.','')
from @table
</code></pre>

blocks|key|246321|text|这是一个从字符串中提取所有数字的版本；也就是说，给定I'm+35+years+old;+I+was+born+in+1982.++The+average+family+has+2.4+children.，它将返回35198224。也就是说，如果你得到的数字数据可能已经被格式化为代码(例如#123,456,789+/+123-00005)，那么它就很好用，但如果你想从文本中提取特定的数字(例如，相对于数字/仅仅是数字字符)，那么它就不合适了。此外，它只处理数字，所以不会返回负号(-)或句点(+.)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|246322|declare+@table+table+(id+bigint+not+null+identity+(1,1),+data+nvarchar(max))+
insert+@table+(data)+
values+('hello+123+its+45613+then')+--outputs:+12345613
,('1+some+other+string+98+example+4')+--outputs:+1984
,('AB+ABCDE+#+123')+--outputs:+123+
,('ABCDE#+123')+--outputs:+123
,('AB:+ABC#+123')+--outputs:+123
;+with+NonNumerics+as+(
++++select+id
++++,+data+original
++++--the+below+line+replaces+all+digits+with+blanks
++++,+replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(data,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')+nonNumeric
++++from+@table
)
--each+iteration+of+the+below+CTE+removes+another+non-numeric+character+from+the+original+string,+putting+the+result+into+the+numerics+column
,+Numerics+as+(
++++select+id
++++,+replace(original,+substring(nonNumeric,1,1),+'')+numerics
++++,+replace(nonNumeric,+substring(nonNumeric,1,1),+'')+charsToreplace
++++,+len(replace(nonNumeric,+substring(nonNumeric,1,1),+''))+charsRemaining
++++from+NonNumerics

++++union+all

++++select+id
++++,+replace(numerics,+substring(charsToreplace,1,1),+'')+numerics
++++,+replace(charsToreplace,+substring(charsToreplace,1,1),+'')+charsToreplace
++++,+len(replace(charsToreplace,+substring(charsToreplace,1,1),+''))+charsRemaining
++++from+Numerics
++++where+charsRemaining+>+0
)
--we+select+only+those+strings+with+`charsRemaining=0`;+i.e.+the+rows+for+which+all+non-numeric+characters+have+been+removed;+there+should+be+1+row+returned+for+every+1+row+in+the+original+data+set.
select+*+from+Numerics+where+charsRemaining+=+0|code-block|syntax|javascript|246323|这段代码的工作原理是将给定字符串中的所有数字(即我们需要的字符)替换为空格。然后遍历原始字符串(包括数字)，删除剩下的所有字符(即非数字字符)，从而只留下数字。|246324|我们分两个步骤来做这件事，而不是首先删除所有非数字字符，因为只有10位数字，而有大量可能的字符；所以替换这个小列表相对较快；然后给我们一个实际存在于字符串中的非数字字符的列表，所以我们可以替换那个小集合。|246325|该方法使用递归SQL，使用公用表表达式(CTE)。|246326|entityMap^0|Q|23|2Y|8|40|C|4F|9|6P|1|6W|1|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]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|17|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|18|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|R|$]]

Here's a version which pulls all digits from a string; i.e. given <code>I'm 35 years old; I was born in 1982. The average family has 2.4 children.</code> this would return <code>35198224</code>. i.e. it's good where you've got numeric data which may have been formatted as a code (e.g. <code>#123,456,789</code> / <code>123-00005</code>), but isn't appropriate if you're looking to pull out specific numbers (i.e. as opposed to digits / just the numeric characters) from the text. Also it only handles digits; so won't return negative signs (<code>-</code>) or periods <code>.</code>).

<pre><code>declare @table table (id bigint not null identity (1,1), data nvarchar(max)) 
insert @table (data) 
values ('hello 123 its 45613 then') --outputs: 12345613
,('1 some other string 98 example 4') --outputs: 1984
,('AB ABCDE # 123') --outputs: 123 
,('ABCDE# 123') --outputs: 123
,('AB: ABC# 123') --outputs: 123
; with NonNumerics as (
 select id
 , data original
 --the below line replaces all digits with blanks
 , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(data,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') nonNumeric
 from @table
)
--each iteration of the below CTE removes another non-numeric character from the original string, putting the result into the numerics column
, Numerics as (
 select id
 , replace(original, substring(nonNumeric,1,1), '') numerics
 , replace(nonNumeric, substring(nonNumeric,1,1), '') charsToreplace
 , len(replace(nonNumeric, substring(nonNumeric,1,1), '')) charsRemaining
 from NonNumerics

 union all

 select id
 , replace(numerics, substring(charsToreplace,1,1), '') numerics
 , replace(charsToreplace, substring(charsToreplace,1,1), '') charsToreplace
 , len(replace(charsToreplace, substring(charsToreplace,1,1), '')) charsRemaining
 from Numerics
 where charsRemaining &gt; 0
)
--we select only those strings with `charsRemaining=0`; i.e. the rows for which all non-numeric characters have been removed; there should be 1 row returned for every 1 row in the original data set.
select * from Numerics where charsRemaining = 0
</code></pre>

This code works by removing all the digits (i.e. the characters we want) from a the given strings by replacing them with blanks. Then it goes through the original string (which includes the digits) removing all of the characters that were left (i.e. the non-numeric characters), thus leaving only the digits.

The reason we do this in 2 steps, rather than just removing all non-numeric characters in the first place is there are only 10 digits, whilst there are a huge number of possible characters; so replacing that small list is relatively fast; then gives us a list of those non-numeric characters which actually exist in the string, so we can then replace that small set.

The method makes use of recursive SQL, using common table expressions (CTEs).

blocks|key|30198|text|如果你的服务器支持翻译功能(在sql+server上，它可以在sql+server+2017%2B和sql+azure上使用)，这里有一个很好的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|30199|首先，它将所有非数字字符替换为@字符。然后，它删除所有@字符。您可能需要添加已知可能出现在TRANSLATE调用的第二个参数中的其他字符。|30200|select+REPLACE(TRANSLATE([Col],+'abcdefghijklmnopqrstuvwxyz%2B()-+,#%2B',+'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'),+'@',+'')|code-block|syntax|javascript|30201|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 is an elegant solution if your server supports the TRANSLATE function (on sql server it's available on sql server 2017+ and also sql azure).

First, it replaces any non numeric characters with a @ character.
Then, it removes all @ characters.
You may need to add additional characters that you know may be present in the second parameter of the TRANSLATE call.

<pre><code>select REPLACE(TRANSLATE([Col], 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')
</code></pre>

blocks|key|30235|text|我为此创建了一个函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|30236|Create+FUNCTION+RemoveCharacters+(@text+varchar(30))
RETURNS+VARCHAR(30)
AS
BEGIN
declare+@index+as+int+
declare+@newtexval+as+varchar(30)
set+@index+=+(select+PATINDEX('%25[A-Z.-/?]%25',+@text))
if+(@index+=0)
begin+
return+@text
end
else
begin+
set+@newtexval++=+(select+STUFF+(+@text+,+@index+,+1+,+''+))
return+dbo.RemoveCharacters(@newtexval)
end
return+0
END
GO|code-block|syntax|javascript|30237|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 have created a function for this 

<pre><code>Create FUNCTION RemoveCharacters (@text varchar(30))
RETURNS VARCHAR(30)
AS
BEGIN
declare @index as int 
declare @newtexval as varchar(30)
set @index = (select PATINDEX('%[A-Z.-/?]%', @text))
if (@index =0)
begin 
return @text
end
else
begin 
set @newtexval = (select STUFF ( @text , @index , 1 , '' ))
return dbo.RemoveCharacters(@newtexval)
end
return 0
END
GO
</code></pre>

blocks|key|246422|text|这就是答案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|246423|DECLARE+@t+TABLE+(tVal+VARCHAR(100))

INSERT+INTO+@t+VALUES('123')
INSERT+INTO+@t+VALUES('123S')
INSERT+INTO+@t+VALUES('A123,123')
INSERT+INTO+@t+VALUES('a123..A123')


;WITH+cte+(original,+tVal,+n)
+++++AS
+++++(
+++++++++SELECT+t.tVal+AS+original,
++++++++++++++++LOWER(t.tVal)++AS+tVal,
++++++++++++++++65+++++++++++++AS+n
+++++++++FROM+++@t+++++++++++++AS+t
+++++++++UNION+ALL
+++++++++SELECT+tVal+AS+original,
++++++++++++++++CAST(REPLACE(LOWER(tVal),+LOWER(CHAR(n)),+'')+AS+VARCHAR(100)),
++++++++++++++++n+%2B+1
+++++++++FROM+++cte
+++++++++WHERE++n+<=+90
+++++)

SELECT+t1.tVal++AS+OldVal,
+++++++t.tval+++AS+NewVal
FROM+++(
+++++++++++SELECT+original,
++++++++++++++++++tVal,
++++++++++++++++++ROW_NUMBER()+OVER(PARTITION+BY+tVal+%2B+original+ORDER+BY+original)+AS+Sl
+++++++++++FROM+++cte
+++++++++++WHERE++PATINDEX('%25[a-z]%25',+tVal)+=+0
+++++++)+t
+++++++INNER+JOIN+@t+t1
++++++++++++ON++t.original+=+t1.tVal
WHERE++t.sl+=+1|code-block|syntax|javascript|246424|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|$]]

Here is the answer:

<pre><code>DECLARE @t TABLE (tVal VARCHAR(100))

INSERT INTO @t VALUES('123')
INSERT INTO @t VALUES('123S')
INSERT INTO @t VALUES('A123,123')
INSERT INTO @t VALUES('a123..A123')


;WITH cte (original, tVal, n)
 AS
 (
 SELECT t.tVal AS original,
 LOWER(t.tVal) AS tVal,
 65 AS n
 FROM @t AS t
 UNION ALL
 SELECT tVal AS original,
 CAST(REPLACE(LOWER(tVal), LOWER(CHAR(n)), '') AS VARCHAR(100)),
 n + 1
 FROM cte
 WHERE n &lt;= 90
 )

SELECT t1.tVal AS OldVal,
 t.tval AS NewVal
FROM (
 SELECT original,
 tVal,
 ROW_NUMBER() OVER(PARTITION BY tVal + original ORDER BY original) AS Sl
 FROM cte
 WHERE PATINDEX('%[a-z]%', tVal) = 0
 ) t
 INNER JOIN @t t1
 ON t.original = t1.tVal
WHERE t.sl = 1
</code></pre>

blocks|key|30283|text|在您的例子中，#似乎总是在#符号之后，所以将CHARINDEX()与LTRIM()和RTRIM()一起使用可能会执行得最好。但这里有一个有趣的方法，可以去掉任何非数字。它使用计数表和数字表来限制接受的字符，然后使用XML技术将不含非数字字符的字符串连接回单个字符串。这项技术的巧妙之处在于，它可以扩展到包括任何允许的字符，并剥离任何不允许的字符。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|30284|DECLARE+@ExampleData+AS+TABLE+(Col+VARCHAR(100))
INSERT+INTO+@ExampleData+(Col)+VALUES+('AB+ABCDE+#+123'),('ABCDE#+123'),('AB:+ABC#+123')

DECLARE+@Digits+AS+TABLE+(D+CHAR(1))
INSERT+INTO+@Digits+(D)+VALUES+('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')

;WITH+cteTally+AS+(
SELECT
++++I+=+ROW_NUMBER()+OVER+(ORDER+BY+(SELECT+NULL))
FROM
++++@Digits+d10
++++CROSS+APPLY+@Digits+d100
++++--add+more+cross+applies+to+cover+longer+fields+this+handles+100
)

SELECT+*
FROM
++++@ExampleData+e
++++OUTER+APPLY+(
++++SELECT+CleansedPhone+=+CAST((
++++SELECT+TOP+100
+++++++SUBSTRING(e.Col,t.I,1)
++++FROM
+++++++cteTally+t
+++++++INNER+JOIN+@Digits+d
+++++++ON+SUBSTRING(e.Col,t.I,1)+=+d.D
++++WHERE
+++++++I+<=+LEN(e.Col)
++++ORDER+BY
+++++++t.I
++++FOR+XML+PATH(''))+AS+VARCHAR(100)))+o|code-block|syntax|javascript|30285|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|$]]

In your case It seems like the # will always be after teh # symbol so using CHARINDEX() with LTRIM() and RTRIM() would probably perform the best. But here is an interesting method of getting rid of ANY non digit. It utilizes a tally table and table of digits to limit which characters are accepted then XML technique to concatenate back to a single string without the non-numeric characters. The neat thing about this technique is it could be expanded to included ANY Allowed characters and strip out anything that is not allowed.

<pre><code>DECLARE @ExampleData AS TABLE (Col VARCHAR(100))
INSERT INTO @ExampleData (Col) VALUES ('AB ABCDE # 123'),('ABCDE# 123'),('AB: ABC# 123')

DECLARE @Digits AS TABLE (D CHAR(1))
INSERT INTO @Digits (D) VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')

;WITH cteTally AS (
SELECT
 I = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
 @Digits d10
 CROSS APPLY @Digits d100
 --add more cross applies to cover longer fields this handles 100
)

SELECT *
FROM
 @ExampleData e
 OUTER APPLY (
 SELECT CleansedPhone = CAST((
 SELECT TOP 100
 SUBSTRING(e.Col,t.I,1)
 FROM
 cteTally t
 INNER JOIN @Digits d
 ON SUBSTRING(e.Col,t.I,1) = d.D
 WHERE
 I &lt;= LEN(e.Col)
 ORDER BY
 t.I
 FOR XML PATH('')) AS VARCHAR(100))) o
</code></pre>

blocks|key|30312|text|您可以创建SQL+CLR标量函数，以便能够使用像替换模式这样的正则表达式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|30313|Here你可以找到如何创建这样的函数的例子。|offset|length|30314|拥有这样的函数只需以下几行就可以解决这个问题：|30315|SELECT+[dbo].[fn_Utils_RegexReplace]+('AB+ABCDE+#+123',+'[%5E0-9]',+'');
SELECT+[dbo].[fn_Utils_RegexReplace]+('ABCDE#+123',+'[%5E0-9]',+'');
SELECT+[dbo].[fn_Utils_RegexReplace]+('AB:+ABC#+123',+'[%5E0-9]',+'');|code-block|syntax|javascript|30316|更重要的是，您将能够解决更复杂的问题，因为正则表达式将直接在T-SQL语句中带来全新的选项世界。|30317|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/31693412/1080354^0|0|0|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@$D|Y|E|Z|1|10]]|A|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|12|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

You can create SQL CLR scalar function in order to be able to use regular expressions like replace patterns. 

<a href="https://stackoverflow.com/a/31693412/1080354">Here</a> you can find example of how to create such function. 

Having such function will solve the issue with just the following lines:

<pre><code>SELECT [dbo].[fn_Utils_RegexReplace] ('AB ABCDE # 123', '[^0-9]', '');
SELECT [dbo].[fn_Utils_RegexReplace] ('ABCDE# 123', '[^0-9]', '');
SELECT [dbo].[fn_Utils_RegexReplace] ('AB: ABC# 123', '[^0-9]', '');
</code></pre>

More important, you will be able to solve more complex issues as the regular expressions will bring a whole new world of options directly in your T-SQL statements.

blocks|key|30329|text|CREATE+FUNCTION+FN_RemoveNonNumeric+(@Input+NVARCHAR(512))
RETURNS+NVARCHAR(512)
AS
BEGIN
DECLARE+@Trimmed+NVARCHAR(512)

SELECT+@Trimmed+=+@Input

WHILE+PATINDEX('%25[%5E0-9]%25',+@Trimmed)+>+0
++++SELECT+@Trimmed+=+REPLACE(@Trimmed,+SUBSTRING(@Trimmed,+PATINDEX('%25[%5E0-9]%25',+@Trimmed),+1),+'')

RETURN+@Trimmed
END

GO

SELECT+dbo.FN_RemoveNonNumeric('ABCDE#+123')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|30330|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>CREATE FUNCTION FN_RemoveNonNumeric (@Input NVARCHAR(512))
RETURNS NVARCHAR(512)
AS
BEGIN
DECLARE @Trimmed NVARCHAR(512)

SELECT @Trimmed = @Input

WHILE PATINDEX('%[^0-9]%', @Trimmed) &gt; 0
 SELECT @Trimmed = REPLACE(@Trimmed, SUBSTRING(@Trimmed, PATINDEX('%[^0-9]%', @Trimmed), 1), '')

RETURN @Trimmed
END

GO

SELECT dbo.FN_RemoveNonNumeric('ABCDE# 123')
</code></pre>

blocks|key|246533|text|DECLARE+@STR+VARCHAR(400)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|246534|DECLARE+@specialchars+VARCHAR(50)+=+'%25~，@，#，$，%25，&，*，(，)，!%5E?:%25‘|246535|SET+@STR+=+'1，45+4,3+68.00-‘|246536|当PATINDEX(+@specialchars，@STR+)>0|246537|-使用Replace函数删除特殊字符|246538|SET+@STR+=REPLACE(REPLACE(+@STR，SUBSTRING(+@STR，PATINDEX(+@specialchars，@STR+)，1+)，'')，'-'，'')|246539|选择@STR|246540|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

DECLARE @STR VARCHAR(400) 

DECLARE @specialchars VARCHAR(50) = '%[~,@,#,$,%,&amp;,*,(,),!^?:]%'

SET @STR = '1, 45 4,3 68.00-'

WHILE PATINDEX( @specialchars, @STR ) > 0

---Remove special characters using Replace function

SET @STR = Replace(Replace(REPLACE( @STR, SUBSTRING( @STR, PATINDEX( @specialchars, @STR ), 1 ),''),'-',''), ' ','')

SELECT @STR

blocks|key|246553|text|很晚才来参加聚会，我发现了下面这些我认为非常有效的方法。如果有人还在找|type|unstyled|depth|inlineStyleRanges|entityRanges|data|246554|SELECT
++++(SELECT+CAST(CAST((
++++++++SELECT+SUBSTRING(FieldToStrip,+Number,+1)
++++++++FROM+master..spt_values
++++++++WHERE+Type='p'+AND+Number+<=+LEN(FieldToStrip)+AND
++++++++++++SUBSTRING(FieldToStrip,+Number,+1)+LIKE+'[0-9]'+FOR+XML+Path(''))
++++AS+xml)+AS+varchar(MAX)))
FROM
++++SourceTable|code-block|syntax|javascript|246555|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|$]]

Pretty late to the party, I found the following which I though worked brilliantialy.. if anyone is still looking
<pre><code>SELECT
 (SELECT CAST(CAST((
 SELECT SUBSTRING(FieldToStrip, Number, 1)
 FROM master..spt_values
 WHERE Type='p' AND Number &lt;= LEN(FieldToStrip) AND
 SUBSTRING(FieldToStrip, Number, 1) LIKE '[0-9]' FOR XML Path(''))
 AS xml) AS varchar(MAX)))
FROM
 SourceTable
</code></pre>

I've got dirty data in a column with variable alpha length. I just want to strip out anything that is not 0-9. 

I do not want to run a function or proc. I have a script that is similar that just grabs the numeric value after text, it looks like this:

<pre><code>Update TableName
set ColumntoUpdate=cast(replace(Columnofdirtydata,'Alpha #','') as int)
where Columnofdirtydata like 'Alpha #%'
And ColumntoUpdate is Null
</code></pre>

I thought it would work pretty good until I found that some of the data fields I thought would just be in the format Alpha # 12345789 are not. 

Examples of data that needs to be stripped

<pre><code>AB ABCDE # 123
ABCDE# 123
AB: ABC# 123
</code></pre>

I just want the 123. It is true that all data fields do have the # prior to the number. 

I tried substring and PatIndex, but I'm not quite getting the syntax correct or something. Anyone have any advice on the best way to address this?

Select query to remove non-numeric characters

SQL Server

服务器

我在一个具有可变alpha长度的列中有脏数据。我只想去掉任何不是0-9的东西。我不想运行函数或进程。我有一个类似的脚本，它只是在文本之后抓取数值，它看起来像这样：Update TableNameset ColumntoUpdate=cast(replace(Columnofdirtydata,'Alpha #','')...

问选择查询以删除非数字字符
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问选择查询以删除非数字字符EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问选择查询以删除非数字字符
EN