从字符串 sql server 中删除数字

2021-12-13 00:00:00 sql sql-server-2008 sql-server

我有一个大型数据库,我想在其中进行部分字符串搜索.用户将输入字符:JoeBloggs.

I have a large database in which I want to do a part string search. The user will enter characters: JoeBloggs.

为了争论,如果我在数据库中有一个名字Joe 23 Blo Ggs 4.我想删除 A-Z 以外的名称中的所有内容.

For arguments sake if I had a name Joe 23 Blo Ggs 4 in the database. I want to remove everything in the name other than A-Z.

我有 REPLACE(Name, ' ','') 函数来删除空格和 UPPER() 函数来将名称大写.

I have the REPLACE(Name, ' ','') function to remove spaces and the UPPER() function to capitalize the name.

是否有更有效的快速方法可以通过正则表达式来替换 A-Z 以外的任何内容.我无法更改数据库中的值.

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.

推荐答案

第一个选项 -

您可以将 REPLACE() 函数嵌套到 32 层深.它运行得很快.

You can nest REPLACE() functions up to 32 levels deep. It runs fast.

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 -

从数字中移除非数字数据 + SQL

第三个选项 - 如果你想使用正则表达式

3rd option - if you want to use regex

然后http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205

相关文章