Author |
Topic |
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-27 : 14:07:28
|
Hi guys, I have a REPLACE / UPDATE question that is a bit complicated (at least for me)I have a column labeled CommandName, some of the values in this column are preceeded by an 'ALL' or a 'RE', and some values have 'RE RE RE', some of 'RE RE RE RE RE RE RE' that are at the left most characters of the string. I want a query that will remove either 'ALL', 'RE' (as many times as it appears) and keep the remaining string to the left of it. I would also like this query to leave all other strings alone that are not preceeded by 'ALL' or 'RE.'I found this from this forum and thought maybe something like this could help. UPDATE dbo.authorsSET city = replace(city, 'Salt', 'Olympic')WHERE city LIKE 'Salt%'; Thank you in advance. |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-27 : 14:16:11
|
How about some sample data and expected outout? Here is a quick bit of sample data I made up. Given this data, what would you want the output to be? Are there other cases that need to be considered or ignored?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')SELECT *FROM @Foo http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-27 : 14:28:45
|
Hi, Thanks for the reply. One thing to keep in mind is that there are a couple hundred unique values for the CommandName column. Here is some sample data / outputCommandName:cmdDoThisRE RE cmdDoWhatRE RE RE RE RE RE cmdHellowALL cmdImDoneAlreadycmdIcalledTwiceLastNightcmdHappyHourcmdJustHavingSomeFunOutput after query...CommandNamecmdDoThiscmdDoWhatcmdHellowcmdImDoneAlreadycmdIcalledTwiceLastNightcmdHappyHourcmdJustHavingSomeFun |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-27 : 14:40:54
|
[code]REPLACE(REPLACE(CommandName,'RE ',''),'ALL ','')[/code] |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-27 : 15:10:23
|
Anything worth doing is worth overdoing,SELECT CommandName, CASE WHEN PATINDEX('%[^RE ]%',CommandName) < 2 -- AND substring(CommandName,PATINDEX('%[^RE ]%',CommandName),100) > 1 THEN substring ( substring(CommandName,PATINDEX('%[^ALL ]%',CommandName),100) ,PATINDEX('%[^RE ]%',substring(CommandName,PATINDEX('%[^ALL ]%',CommandName),100)) ,100) ELSE CASE WHEN PATINDEX('%[^ALL ]%',CommandName) < 2 THEN substring ( substring(CommandName,PATINDEX('%[^RE ]%',CommandName),100) ,PATINDEX('%[^ALL ]%',substring(CommandName,PATINDEX('%[^RE ]%',CommandName),100)) ,100) END ENDFROM @fooJimEveryday I learn something that somebody else already knew |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-27 : 15:16:43
|
Thanks James. A question before I test it. What if the word "ALL" is in the middle of one of these command names i.e. cmdHeyALLpeople? Will ALL be removed from the middle? I only want ALL and RE to be removed if it is to the left of the string. Also, will this query take care of RE no matter how many times it appears to the left of a string? |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-27 : 15:20:27
|
Thanks to you as well jimf. Didn't see this till now. WOW! I suppose I have the same questions for you asl well. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-27 : 15:28:08
|
Test the solutiions and see which one fits the bill. If there's a CommandName value you specifically want to test, add it to the sample date Lamprey provided.JimEveryday I learn something that somebody else already knew |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-27 : 15:56:40
|
This didn't work. Am I doing something wrong?REPLACE(REPLACE(CommandName, 'RE ', ''), 'ALL ', '')FROM TableName |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-27 : 16:09:03
|
quote: Originally posted by sccrsurfer This didn't work. Am I doing something wrong?REPLACE(REPLACE(CommandName, 'RE ', ''), 'ALL ', '')FROM TableName
did it give the wrong results? Can you post an example? This would remove all occurrences of "RE" followed by a space or "ALL" followed by a space. So if you had "Can I Call you?", it will return "Can ICyou". |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-27 : 16:12:45
|
James K, it gave this feedback. Sorry for not being clear.Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'REPLACE'. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-27 : 16:35:20
|
You have to use one of these:-- If you want to update the tableUPDATE TableName SET CommandName = REPLACE(REPLACE(CommandName,'RE ',''),'ALL ','')-- or if you want to leave the table alone and select the amended stringsSELECT REPLACE(REPLACE(CommandName,'RE ',''),'ALL ','')FROM TableName |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-27 : 16:53:18
|
Ah, ok. Thanks for that. I realize I wont be able to use your solution (though much appreciated) because the word "Call" is in some of these commands, where the 'all' substring is present and would be removed. Unless I'm totally not understanding what is happening in your query... |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-27 : 16:56:20
|
JimF, can I just replace SELECT w/ UPDATE for the solution you provided? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 08:42:04
|
Jim's code can be easily converted to do the update - see in red below: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')--SELECT CommandName,UPDATE @Foo SET CommandName = CASE WHEN PATINDEX('%[^RE ]%',CommandName) < 2 -- AND substring(CommandName,PATINDEX('%[^RE ]%',CommandName),100) > 1THENsubstring( substring(CommandName,PATINDEX('%[^ALL ]%',CommandName),100),PATINDEX('%[^RE ]%',substring(CommandName,PATINDEX('%[^ALL ]%',CommandName),100)) ,100) ELSE CASE WHEN PATINDEX('%[^ALL ]%',CommandName) < 2THENsubstring( substring(CommandName,PATINDEX('%[^RE ]%',CommandName),100),PATINDEX('%[^ALL ]%',substring(CommandName,PATINDEX('%[^RE ]%',CommandName),100)) ,100)ENDEND--FROM @fooSELECT * FROM @Foo |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-28 : 09:08:54
|
JamesK, to understand do I need to type every instance where the ALL or RE- can appear? For example in the section where you write('ALL CallThat')('RE-RE-CalledIt)etc....Will I need to find every unique instance where RE and ALL appear and place it in the query? It wont be much trouble, just want to make sure I'm understanding it correctly before testing it so I know what to expect when running the query. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 09:27:58
|
Jim can speak to this better than I can, but from what I see in his query, it will remove all instances of "RE " and "ALL " if they are occurring at the beginning of the string. It will not remove if they are occurring later in the string (as in the second example below), which I think is what you wanted. Also, it seems to leave some instances unremoved (as in example 3):DECLARE @Foo TABLE (CommandName VARCHAR(MAX)) Not sure if that is what you want.INSERT @Foo (CommandName) VALUES ('RE tester'),('tester RE'),('RE ALL RE ALL tester RE') |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-28 : 09:38:41
|
So does 'tester' in ('RE tester') need to be replaced with an actual command name? I'm assuming the answer is yes. I'm going to play with it in a sandbox table I made for myself to see what happens |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 09:46:10
|
You would/should use your actual tables in the sandbox. @Foo was a table that Lamprey constructed, which he, Jim and I have been using to test. |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-28 : 10:02:58
|
I'm getting a an error: It says I'm getting an error on the labeled line below "ERROR ON THIS LINE"The error message is:Msg 102, Level 15, State 1, Line 4Incorrect syntax near ','.So here is some sample dataINSERT @XEM_SANDBOX_SEVAN (CommandName) VALUES ('ALLDrone Client1'), ERROR ON THIS LINE('ALLDrone Client1Function2'),('ALLDrone Client3Function3'),('ALLDrone Client2Function8'), |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-02-28 : 10:29:42
|
Question revised: So I ran one of the simpler REPLACE query to get rid of the RE values. Now that just leaves the ALL values. Can I reduce the PATINDEX query to something like this?select substring(CommandName, patindex('%[^ALL]%',CommandName), 100) |
|
|
Next Page
|