Solved

Update Query in SQL - Needing to Replace wildcard pattern

Posted on 2008-06-17
10
4,751 Views
Last Modified: 2012-08-14
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
Comment
Question by:MLG0001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 21804812
UPDATE SomeTable
SET SomeColumn = LEFT(SomeColumn, PATINDEX('% _ %', SomeColumn) - 1) + MID(SomeColumn, PATINDEX('% _ %', SomeColumn) + 3, LEN(SomeColumn))
WHERE PATINDEX('% _ %', SomeColumn) > 0
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 21804827
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
 

Author Comment

by:MLG0001
ID: 21805224
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 50 total points
ID: 21805345
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
 

Author Comment

by:MLG0001
ID: 21805783
<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
 

Author Comment

by:MLG0001
ID: 21805898
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 75 total points
ID: 21805926
UPDATE tablename
SET name = STUFF(name, PATINDEX('% [a-zA-Z] %', name), 2, '')
WHERE PATINDEX('% [a-zA-Z] %', name) > 0
0
 

Author Closing Comment

by:MLG0001
ID: 31468034
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 21806745
Scott,

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

Regards,

Patrick
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21807430
Thanks.

It supports a basic [] and [^], that's about it :-).  Look under LIKE in BOL for details.
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question