Remove Spaces in SQL Server table data

We have the following data in table1.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 col开发者_StackOverflowumn data once. Please guide. Thanks.

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.

You could also write a function using REPLACE

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

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;

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

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

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

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