Solved

Update Query in SQL - Needing to Replace wildcard pattern

Posted on 2008-06-17
10
4,686 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
  • 4
  • 4
  • 2
10 Comments
 
LVL 92

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 92

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

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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:
ScottPletcher 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 92

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:ScottPletcher
ID: 21807430
Thanks.

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now