从字符串sql server中删除数字

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 4 in the database. I want to remove everything in the name other than A-Z.

为了讨论，如果我在数据库中有一个名字Joe 23 Blo Ggs 4的话。除了A-Z，我想把所有的东西都去掉。

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.

有没有一种更高效快捷的方法可以用正则表达式代替除a - z之外的任何东西。我无法更改数据库中的值。

Thanks in advance

谢谢提前

6 个解决方案

#1

1st option -

第一个选项,

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

可以嵌套至多32层的REPLACE()函数。它跑得快。

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

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

3rd option - if you want to use regeex

第三种选择——如果你想使用regeex

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

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

#2

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

#3

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

#4

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

#5

Quoting part of @Jatin answer with some modifications,

引用部分@Jatin的回答进行一些修改，

use this in your where statement:

在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

#6

-1

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.

关键是将它用作计算列并对其进行索引。如果数据库必须在每次运行查询时都要对大表中的每一行执行它，那么它的速度就不重要了。

#1