删除SQL Server表数据中的空格

We have the following data in table1.column1

我们在表1.column1中有以下数据

A V John 
B V S Shultz
S Hanks
K L C Gove, P S Murphy

We need to remove space between one letter words. The data after conversion should like

我们需要去掉一个字母单词之间的空格。转换后的数据应该喜欢

AV John
BVS Shultz
S Hanks
KLC Gove, PS Murphy

Is it possible to write sql query regex to clean up the whole column data once. Please guide. Thanks.

是否可以编写sql查询regex来一次清理整个列数据。请指导。谢谢。

4 个解决方案

#1

You might want to take a look at Using regular expression within a stored procedure

您可能想了解如何在存储过程中使用正则表达式

Is describes how to use regex in a SQL Server client.

描述如何在SQL服务器客户机中使用regex。

You could also write a function using REPLACE

还可以使用REPLACE编写函数

#2

It is possible with T-SQL but no one would call it beautiful ...

使用T-SQL是可能的，但是没有人会说它很漂亮……

with cNames as (
select id=cast(id as tinyint), name=cast(name as varchar(50))
from   (values (1, 'A V John'), (2, 'B V S Shultz'), 
       (3, 'S Hanks'), (4, 'K L C Gove, P S Murphy')
       ) n (id, name)
),
cNumbers as (
select n=row_number() over (order by (select 1))
from (values (1),(1),(1),(1)) a (n)
cross join (values (1),(1),(1),(1)) b (n)
cross join (values (1),(1),(1),(1)) c (n)
),
cNameparts as (
select  c.id, n.n, c.name, 
        namepart=substring(c.name,n.n,charindex(' ',c.name+' ',n.n)-n.n)
from    cNames c
inner join cNumbers n
    on  substring(' '+c.name,n.n,1) = ' '
    and n.n < len(c.name)
)
select  name=
        (select case when len(namepart)>1 then ' ' else '' end +
                namepart +
                case when right(namepart,1)=',' then ' ' else '' end
        from    cNameparts np
        where   np.id = c.id
        for xml path(''))
from    cNames c
order by c.id;

#3

I was looking for an answer to a similar problem I had and this seem like it solved my issue where I had

我在寻找一个类似问题的答案，这似乎解决了我的问题

Tom              Smith
Tom                   Smith
Tom                       Smith
replace(replace(replace(NAME,' ','<>'),'><',''),'<>',' ')

will all be Tom Smith based on this link

所有的人都是基于这个链接的汤姆·史密斯吗

#4

Here's the regex you need to match the space between 2 adjacent single letters, then you'd replace that with "" (a blank):

这里是regex，您需要匹配两个相邻的单个字母之间的空格，然后用“”(空格)替换它:

"(?<(^| )[a-zA-Z]) (?=[a-zA-Z]( |$))"

Note that this pattern doesn't include either of the letters - it's just the space between.

注意，这个模式不包含任何一个字母——它只是中间的空格。

#1