SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Replace - but perhaps a bit complicated
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 02/28/2013 :  11:15:21  Show Profile  Reply with Quote
If the actual data is like 'ALLDrone Client1Function2', what do you want to get when you replace?

Let me go back to your original question, where the beginning of the string would have "RE " or "ALL " or multiples of those in any combination. To replace all of those, you can do an update like this:
DECLARE @Foo TABLE (CommandName VARCHAR(MAX))

INSERT @Foo (CommandName) VALUES 
('RE tester'),
('RE tester RE'),
('tester RE'),
('RE RE RE tester'),
('RE retester'),
('ALL call me reguarding...'),
('ALL call me ALL at once'),
('call me RE'),
('ALL ALL ALL call me'),
('ALL call me RE: A Tester'),
('ALL RE tester call'),
('RE ALL tester call'),
('RE ALL RE ALL something else added by JK'),
('RE ALL ALL ALL RE something else again RE ALL ALL');

UPDATE @Foo SET 
	CommandName = STUFF(CommandName,1,PATINDEX('%[^(RE|ALL) ]%',CommandName)-1,'');
	
SELECT * FROM @Foo;
Change the values in the sample table to some of your typical data and see if they all work correctly. If not, post the cases where they fail?
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/28/2013 :  13:58:46  Show Profile  Reply with Quote
Well I figured it out, with your, Lampey and JimF's major help of course. Thanks for staying. What I wanted was to Remove the RE and ALL if it was the left most substring and keep everything to the right of it. I was lucky because any string that had ALL also had a D with no space i.e. ALLD. So I just added a tweak to your solution and it worked. Perhaps not the most sophisticated way to go about things, but it got the job done.

UPDATE XEM_usage SET
CommandName = REPLACE(REPLACE(REPLACE(CommandName,'RE-',''),'ALLD','D'), 'ALLRE-','')
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 02/28/2013 :  14:38:46  Show Profile  Reply with Quote
Glad you finally got it working :)

Can there be instances where any of those 3 strings (RE-, ALLD, ALLRE-) would be somewhere in the rest of the string?
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/28/2013 :  15:50:26  Show Profile  Reply with Quote
Not RE- or ALLD, but ALL was in some of the strings. For example cmdHallmarkMailer
cmdBatchRoutineCall

If I just ran the replace query with ALL I would be left with:
cmdHmarkMailer
cmdBatchRoutineCll.

I switched ALL in the query you gave me because ALL had D right after it. I wanted to keep the D so I just replaced ALLD with D and it worked.

That being said, I would like to learn how the PATINDEX function works.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/28/2013 :  19:48:52  Show Profile  Reply with Quote
Sorry I abandoned the thread, work happens. PATINDEX finds the index(the position of the pattern in the string) of the pattern match. So you can find a group of characters and not just one. It's kind of like a LIKE statement. My query said
"Where does the string start where the first thing isn't 'ALL ' or 'RE ', and make a new string out of everything to the right of that". The NOT LIKE is the "^" you see in my query and James' final query (BTW, just learned the "|", it seems like everyday I learned something that ..). So "ALL RE Call RE ALL" all would become "Call RE All" and not just Call, and "Call RE ALL" would stay the same. I hope that makes some sense. PATINDEX is awfully useful for those queries of "My data is stupid but I have to deal with it, so how do I get the good stuff out of it?"

Jim


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

lucyming
Starting Member

Austria
4 Posts

Posted - 04/30/2013 :  03:00:18  Show Profile  Reply with Quote
Thanks for that.


__________________________________________________
Diablo 3 Gold;Aion Kinah;Diablo 3 Gold Kaufen;Guild Wars 2 Gold
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000