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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/27/2013 :  14:07:28  Show Profile  Reply with Quote
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

4614 Posts

Posted - 02/27/2013 :  14:16:11  Show Profile  Reply with Quote
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
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/27/2013 :  14:28:45  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/27/2013 :  14:40:54  Show Profile  Reply with Quote
REPLACE(REPLACE(CommandName,'RE ',''),'ALL ','')
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/27/2013 :  15:10:23  Show Profile  Reply with Quote
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
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/27/2013 :  15:16:43  Show Profile  Reply with Quote
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?
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/27/2013 :  15:20:27  Show Profile  Reply with Quote
Thanks to you as well jimf. Didn't see this till now. WOW! I suppose I have the same questions for you asl well.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/27/2013 :  15:28:08  Show Profile  Reply with Quote
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
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/27/2013 :  15:56:40  Show Profile  Reply with Quote
This didn't work. Am I doing something wrong?

REPLACE(REPLACE(CommandName, 'RE ', ''), 'ALL ', '')
FROM TableName
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/27/2013 :  16:09:03  Show Profile  Reply with Quote
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".
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/27/2013 :  16:12:45  Show Profile  Reply with Quote
James K, it gave this feedback. Sorry for not being clear.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'REPLACE'.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/27/2013 :  16:35:20  Show Profile  Reply with Quote
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
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/27/2013 :  16:53:18  Show Profile  Reply with Quote
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...
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/27/2013 :  16:56:20  Show Profile  Reply with Quote
JimF, can I just replace SELECT w/ UPDATE for the solution you provided?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/28/2013 :  08:42:04  Show Profile  Reply with Quote
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
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/28/2013 :  09:08:54  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/28/2013 :  09:27:58  Show Profile  Reply with Quote
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')
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/28/2013 :  09:38:41  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 02/28/2013 :  09:46:10  Show Profile  Reply with Quote
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.
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/28/2013 :  10:02:58  Show Profile  Reply with Quote
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'),
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/28/2013 :  10:29:42  Show Profile  Reply with Quote
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)
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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.11 seconds. Powered By: Snitz Forums 2000