I have a table with a name column in it that contains names like:
A & A Turf
C & D Railways
D & B Railways
I have the following query that will get me the correct columns I want
select name from table where patindex('_ & _ %', name) > 0
What I need to accomplish is making anything with that type of pattern collapsed. Like this
A&A Turf
C&D Railways
D&B Railways
I'm also looking how I can do the same thing with single letter followed by a space followed by another single letter followed by a space then words with more then one letter like this
A F Consulting -> AF Consulting
D B Catering -> DB Consulting
but only if the single letter stuff is at the beginning of the value.
Example would be if the name has the pattern mentioned above anywhere in the name then don't do anything unless it's at the beginning
ALBERS, J K -> ALBERS, J K
This would not change because it's a name and it's not at the beginning.
So something like this would be the desired result:
Original Name New Name Rule ____________ __________ ___________ A & K Consulting A&K Consulting Space Taken out between & for single characters C B Finance CB Finance space taken out only if beginning beginning Albert J K Albert J K not at beginning so left alone
This can be done without PATINDEX. Because what needs to be replaced is at the start, and has fixed patterns. So you already know the positions.
Example snippet:
DECLARE @Table TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(30));
INSERT INTO @Table (name) VALUES
('A & K Consulting'),
('C B Finance'),
('Albert J K'),
('Foo B & A & R');
SELECT
name AS OldName,
(CASE
WHEN name LIKE '[A-Z] [A-Z] %' THEN STUFF(name,2,1,'')
WHEN name LIKE '[A-Z] & [A-Z] %' THEN STUFF(name,2,3,'&')
ELSE name
END) AS NewName
FROM @Table;
Test on rextester here