Replace - but perhaps a bit complicated

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

General SQL Server Forums

New to SQL Server Programming

Replace - but perhaps a bit complicated

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.authors
SET 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 / 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
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
END
FROM @foo

Jim

Everyday 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.

Jim

Everyday 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 1
Incorrect 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 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

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) > 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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources