Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Update Query in SQL - Needing to Replace wildcard

Author  Topic 

mlg0001
Starting Member

1 Post

Posted - 2008-06-17 : 13:18:43
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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 13:31:19

update your_Table set data =stuff(data,charindex(' ',data),charindex(' ',substring(data,charindex(' ',data)+1,len(data))),'')

Also consider storing first name and last name in seperate columns


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -