Update Query in SQL - Needing to Replace wildcard pattern

I am trying to do an update query in SQL 2k that updates a name field and removes only the pattern of space middle initial space. I do not want it to strip out any additional names in the field. Some of the values in the name field have 2 last names that are not hypenated - I need to keep this the same.
I cannot get SQL to recognize the wildcard pattern I am searching for and remove it.
I want a name like John S Doe to be John Doe. I want a name like John S Doe Smith to update to John Doe Smith. Only single alpha characters between two spaces need to be updated.
MLG0001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
UPDATE SomeTable
SET SomeColumn = LEFT(SomeColumn, PATINDEX('% _ %', SomeColumn) - 1) + MID(SomeColumn, PATINDEX('% _ %', SomeColumn) + 3, LEN(SomeColumn))
WHERE PATINDEX('% _ %', SomeColumn) > 0
0
Patrick MatthewsCommented:
MLG0001,

Be advised that that is no tremendously robust.  Consider: John R R Tolkien.  My suggestion would remove
the first middle initial, but not the second.

Regards,

Patrick
0
MLG0001Author Commented:
Thanks for the quick reply when I tried to run this query,  I get MID is not a recognized function name. When I try this. Also % _ %  in the patindex - how is this finding the alpha character? Do I need to change this _ to % or to a specific alpha character? I'd like it to be a wild card that represents any value that is one character in length between two spaces.  Thanks again for the fast response!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Patrick MatthewsCommented:
MLG0001,

Sorry, replace MID with SUBSTRING.  MID works for Access; SQL Server needs SUBSTRING.   :)

That wild card does in fact match any single character, but why would you have a numeral in the middle
of a name field?  And with respect, why was a single column used for a full name, instead of the best
practice of using separate columns for each name part?

Your application should already be using some sort of validation to prevent numerals from coming in
in the middle of the string already...

Regards,

Patrick
0
MLG0001Author Commented:
<sigh> This database was set up to work one way and now data received has been changed by the suits at corporate making the decisions LOL. I totally agree to separate names...it would make this issue not even relevant!  Yeah so now the IT geeks at the bottom have to find work arounds for inconsistencies received from non-technical decision makers from up top.  
I do have something in the import code that is taking out numbers, but I am not aware of using the _ to look for any character format.
I tried this - it does work but it is not allowing a space in between the first name and last name - should it be + 2 intead of 3 in the substring to retain on of the space values?
0
MLG0001Author Commented:
When I try to do a select statement to see how it would return the values using this concatenation method, it is returning an error  Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

SELECT LEFT(Name, PATINDEX('% _ %', Name)-1) + SUBSTRING(Name, PATINDEX('% _ %', Name) + 2, LEN(Name)) AS NAME
from tblSomeTable

But when I do a select statement as shown below it returns over 77k records.
SELECT     *
FROM         tblResults
WHERE     (PATINDEX('% _ %', Name) > 0)

0
Scott PletcherSenior DBACommented:
UPDATE tablename
SET name = STUFF(name, PATINDEX('% [a-zA-Z] %', name), 2, '')
WHERE PATINDEX('% [a-zA-Z] %', name) > 0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MLG0001Author Commented:
Both of these examples do work but I felt the STUFF Function was more efficient and less effort trying to concatenate strings. I appreciate all responses - they were quick to be received and very easy to understand. Thank you both for your help!
0
Patrick MatthewsCommented:
Scott,

Nicely done.  I wasn't sure how deeply SQL Server gets into "pseudo regular expressions" :)

Regards,

Patrick
0
Scott PletcherSenior DBACommented:
Thanks.

It supports a basic [] and [^], that's about it :-).  Look under LIKE in BOL for details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.