Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update Query in SQL - Needing to Replace wildcard pattern

Posted on 2008-06-17
10
Medium Priority
?
4,769 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 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 70

Accepted Solution

by:
Scott Pletcher earned 300 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 70

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

715 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