Anyone know how to do this with SQL and replacing a hyphen and masking

Question

I have to update a lot of data. I have the following problem.

In the database there is a [Name] field that contains names in various formats what they would like to have done is, if there is a 1 character followed by an - then another character collapse those together like this

A-K Consulting LLC -> AK Consulting LLC
4-T Catfish -> 4T Catfish
but L-Tech LLC would stay the same.

I have tried using
replace(name, '%-%', char(10))
but that leaves the character for a backspace in the field and that's not the desired result.

Never dealt with trying to take out a hyphen and then collapse just that part in the middle of a column. anyone have any thoughts.

Answer 1

Here is one approach where we parse the name and then perform a conditional aggregation based on a pattern of _-_

Full disclosure: It may not perform well on a large table.

Example

Declare @YourTable Table ([Name] varchar(50))
Insert Into @YourTable Values 
 ('A-K Consulting LLC')
,('4-T Catfish')
,('L-Tech LLC')

Select A.Name
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select S = Stuff((Select ' ' +case when RetVal like '_-_' then replace(RetVal,'-','') else RetVal end
                  From (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace((Select replace(A.Name,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
                       ) B1
                  Order by RetSeq
                  For XML Path ('')),1,1,'') 
             ) B

Returns

Name                  S
A-K Consulting LLC    AK Consulting LLC
4-T Catfish           4T Catfish
L-Tech LLC            L-Tech LLC

EDIT - If Expecting Only 1 Occurrence --- forgot the ltrim/rtrim

Select A.Name
      ,S =  case when patindex('% _-_ %',' '+Name+' ')>0 
            then ltrim(rtrim(stuff(Name,patindex('% _-_ %',' '+Name+' ')+1,1,'')))
            else Name
            end
 From  @YourTable A

EDIT2 - Based on Jeroen's Insight

Select A.Name
      ,S = ISNULL(STUFF([Name], NULLIF(PATINDEX('% _-_ %', ' ' + [Name] + ' '), 0) + 1, 1, ''), [Name])
 From  @YourTable A