| Author |
 Topic  |
|
sccrsurfer
Starting Member
USA
43 Posts |
 Posted - 02/27/2013 : 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.authors
SET city = replace(city, 'Salt', 'Olympic')
WHERE city LIKE 'Salt%';
Thank you in advance. |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3823 Posts |
Posted - 02/27/2013 : 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
USA
43 Posts |
Posted - 02/27/2013 : 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 / output
CommandName:
cmdDoThis
RE RE cmdDoWhat
RE RE RE RE RE RE cmdHellow
ALL cmdImDoneAlready
cmdIcalledTwiceLastNight
cmdHappyHour
cmdJustHavingSomeFun
Output after query...
CommandName
cmdDoThis
cmdDoWhat
cmdHellow
cmdImDoneAlready
cmdIcalledTwiceLastNight
cmdHappyHour
cmdJustHavingSomeFun
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 02/27/2013 : 14:40:54
|
REPLACE(REPLACE(CommandName,'RE ',''),'ALL ','') |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 02/27/2013 : 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
END
FROM @foo
Jim
Everyday I learn something that somebody else already knew |
|
|
|
sccrsurfer
Starting Member
USA
43 Posts |
Posted - 02/27/2013 : 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
USA
43 Posts |
Posted - 02/27/2013 : 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
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 02/27/2013 : 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.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
sccrsurfer
Starting Member
USA
43 Posts |
Posted - 02/27/2013 : 15:56:40
|
This didn't work. Am I doing something wrong?
REPLACE(REPLACE(CommandName, 'RE ', ''), 'ALL ', '')
FROM TableName |
|
|
|
James K
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 02/27/2013 : 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
USA
43 Posts |
Posted - 02/27/2013 : 16:12:45
|
James K, it gave this feedback. Sorry for not being clear.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'REPLACE'. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 02/27/2013 : 16:35:20
|
You have to use one of these:-- If you want to update the table
UPDATE TableName SET
CommandName = REPLACE(REPLACE(CommandName,'RE ',''),'ALL ','')
-- or if you want to leave the table alone and select the amended strings
SELECT REPLACE(REPLACE(CommandName,'RE ',''),'ALL ','')
FROM TableName |
Edited by - James K on 02/27/2013 16:35:35 |
|
|
|
sccrsurfer
Starting Member
USA
43 Posts |
Posted - 02/27/2013 : 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
USA
43 Posts |
Posted - 02/27/2013 : 16:56:20
|
| JimF, can I just replace SELECT w/ UPDATE for the solution you provided? |
|
|
|
James K
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 02/28/2013 : 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) > 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
END
--FROM @foo
SELECT * FROM @Foo |
|
|
|
sccrsurfer
Starting Member
USA
43 Posts |
Posted - 02/28/2013 : 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
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 02/28/2013 : 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
USA
43 Posts |
Posted - 02/28/2013 : 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
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 02/28/2013 : 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
USA
43 Posts |
Posted - 02/28/2013 : 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 4
Incorrect syntax near ','.
So here is some sample data
INSERT @XEM_SANDBOX_SEVAN (CommandName) VALUES
('ALLDrone Client1'), ERROR ON THIS LINE
('ALLDrone Client1Function2'),
('ALLDrone Client3Function3'),
('ALLDrone Client2Function8'), |
|
|
|
sccrsurfer
Starting Member
USA
43 Posts |
Posted - 02/28/2013 : 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) |
|
|
|
Topic |
|
Replace - but perhaps a bit complicated
