SQL 从字符串sql server中删除数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13240298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove numbers from string sql server
提问by CR41G14
I have a large database in which I want to do a part string search. The user will enter characters: JoeBloggs.
我有一个大型数据库，我想在其中进行部分字符串搜索。用户将输入字符：JoeBloggs.
For arguments sake if I had a name Joe 23 Blo Ggs 4in the database. I want to remove everything in the name other than A-Z.
为了争论，如果我Joe 23 Blo Ggs 4在数据库中有一个名字。我想删除名称中除 AZ 以外的所有内容。
I have the REPLACE(Name, ' ','')function to remove spaces and the UPPER()function to capitalize the name.
我有REPLACE(Name, ' ','')删除空格的UPPER()功能和大写名称的功能。
Is there a more efficient fast way maybe by terms of regex to replace anything other than A-Z. I cannot change the values in the database.
是否有更有效的快速方法可能通过正则表达式来替换 AZ 以外的任何内容。我无法更改数据库中的值。
回答by Jatin
1st option -
第一个选项——
You can nest REPLACE()functions up to 32 levels deep. It runs fast.
您最多可以嵌套REPLACE()32 层深的函数。它运行得很快。
REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE (@str, '0', ''),
'1', ''),
'2', ''),
'3', ''),
'4', ''),
'5', ''),
'6', ''),
'7', ''),
'8', ''),
'9', '')
2nd option -- do the reverse of -
第二个选项 - 做相反的 -
Removing nonnumerical data out of a number + SQL
3rd option - if you want to use regex
第三个选项 - 如果你想使用正则表达式
回答by Ben Anderson
This one works for me
这个对我有用
CREATE Function [dbo].[RemoveNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
Declare @NumRange as varchar(50) = '%[0-9]%'
While PatIndex(@NumRange, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')
Return @Temp
End
and you can use it like so
你可以像这样使用它
SELECT dbo.[RemoveNumericCharacters](Name) FROM TARGET_TABLE
回答by Sutirth
Try below for your query. where val is your string or column name.
试试下面的查询。其中 val 是您的字符串或列名。
CASE WHEN PATINDEX('%[a-z]%', REVERSE(val)) > 1
THEN LEFT(val, LEN(val) - PATINDEX('%[a-z]%', REVERSE(val)) + 1)
ELSE '' END
回答by TheGameiswar
One more approach using Recursive CTE..
另一种使用递归 CTE 的方法..
declare @string varchar(100)
set @string ='te165st1230004616161616'
;With cte
as
(
select @string as string,0 as n
union all
select cast(replace(string,n,'') as varchar(100)),n+1
from cte
where n<9
)
select top 1 string from cte
order by n desc
**Output:**
test
回答by Aaron West
Remove everything after first digit (was adequate for my use case): LEFT(field,PATINDEX('%[0-9]%',field+'0')-1)
删除第一个数字之后的所有内容（对于我的用例来说已经足够了）：LEFT(field,PATINDEX('%[0-9]%',field+'0')-1)
Remove trailing digits: LEFT(field,len(field)+1-PATINDEX('%[^0-9]%',reverse('0'+field))
删除尾随数字：LEFT(field,len(field)+1-PATINDEX('%[^0-9]%',reverse('0'+field))
回答by simsim
Quoting part of @Jatin answer with some modifications,
引用部分@Jatin 回答并进行一些修改，
use this in your wherestatement:
在你的where陈述中使用这个：
SELECT * FROM .... etc.
Where
REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE (Name, '0', ''),
'1', ''),
'2', ''),
'3', ''),
'4', ''),
'5', ''),
'6', ''),
'7', ''),
'8', ''),
'9', '') = P_SEARCH_KEY
回答by Laurence
Not tested, but you can do something like this:
未经测试，但您可以执行以下操作：
Create Function dbo.AlphasOnly(@s as varchar(max)) Returns varchar(max) As
Begin
Declare @Pos int = 1
Declare @Ret varchar(max) = null
If @s Is Not Null
Begin
Set @Ret = ''
While @Pos <= Len(@s)
Begin
If SubString(@s, @Pos, 1) Like '[A-Za-z]'
Begin
Set @Ret = @Ret + SubString(@s, @Pos, 1)
End
Set @Pos = @Pos + 1
End
End
Return @Ret
End
The key is to use this as a computed column and index it. It doesn't really matter how fast you make this function if the database has to execute it against every row in your large table every time you run the query.
关键是将其用作计算列并对其进行索引。如果每次运行查询时数据库都必须针对大表中的每一行执行该函数，那么创建该函数的速度实际上并不重要。
