Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4780
  • Last Modified:

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.
0
MLG0001
Asked:
MLG0001
  • 4
  • 4
  • 2
2 Solutions
 
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now