SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tricky REPLACE problem


Tricky REPLACE problem

Author
Message
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 996
I've been poring over how to handle this for a few hours without using some kind of horrifying loop, and having failed to find a solution, I turn to the masters.

I have a column containing strings that look like some variation on this:

A#+AB#+BA#+B#

The letters before the # can be any combination of one or two letters. I need to replace all instances of 'A#' with 'C#'. Simple enough, except for the fact that 'BA#' must remain untouched. Obviously a simple REPLACE(@String, 'A#', 'C#') will return this:

C#+AB#+BC#+B#

That's wrong. I need:

C#+AB#+BA#+B#

It would be perfect if there was a function like:

REPLACE(@String, '%[^A-Z]A#%', 'C#')

But as far as I know there isn't. (Even if there was, that would also exclude the A# at the beginning of the string, as there is no character before it.) Can anyone offer a suggestion?

Bonus points if that solution does not involve a CLR. ;-)

Thanks!
Ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Tom Brown
Tom  Brown
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1817 Visits: 1488
replace the untouchable 'BA#' first, then do the main replace, then put the 'BA#' back


SELECT REPLACE(REPLACE(REPLACE(@STRING, 'BA#', '***'), 'A#', 'C#'), '***', 'BA#')


Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28111 Visits: 39939
the problem with abstracting out the problem is it hides the actual issue.

i suspect that this would involve splitting ont he # sign, cleaning up any elements that meet the criteria i could not figure out, and then using FOR XML to concatenate them back together again.

search for "DelimitedSplit8K" here on SSC to get teh function and the great article on how to use it.
try this, and see how it turns the values into a column of data? i think that's the key here:

with MyTable(ID,SomeString)
AS
(
SELECT 1,'A#+AB#+BA#+B#' UNION ALL
SELECT 2,'C#+AB#+BC#+B#'
)
select * from MyTable
cross apply dbo.delimitedsplit8k(SomeString,'#')




ID SomeString ItemNumber Item
1 A#+AB#+BA#+B# 1 A
1 A#+AB#+BA#+B# 2 +AB
1 A#+AB#+BA#+B# 3 +BA
1 A#+AB#+BA#+B# 4 +B
1 A#+AB#+BA#+B# 5
2 C#+AB#+BC#+B# 1 C
2 C#+AB#+BC#+B# 2 +AB
2 C#+AB#+BC#+B# 3 +BC
2 C#+AB#+BC#+B# 4 +B
2 C#+AB#+BC#+B# 5



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 996
Tom Brown (9/7/2012)
replace the untouchable 'BA#' first, then do the main replace, then put the 'BA#' back


SELECT REPLACE(REPLACE(REPLACE(@STRING, 'BA#', '***'), 'A#', 'C#'), '***', 'BA#')



Well, the problem with that is that I've only given one very limited example. That string could contain anything from AA# through ZA#. I'm not sure this approach could be made to work in that regard. Please correct me if I'm wrong.

Ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16466 Visits: 19086
Another suggestion.

SELECT STUFF( REPLACE( '+' + string, '+A#', '+C#'), 1, 1, '')
FROM(SELECT 'A#+AB#+BA#+B#' AS string) A




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16466 Visits: 19086
@Lowell
Shouldn't the splitting be done on the plus sign(+)? These look as adding formulas.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 996
Lowell (9/7/2012)
the problem with abstracting out the problem is it hides the actual issue.


You're correct, I apologize for confusing the issue by trying to oversimplify it.

This column contains formulas that are parsed out and calculated. These formulas can be written and edited by the end user. A through ZZ represent variables. The plain-language description of these variables is also user-definable (R may represent "Length in feet," for example). After each variable there is a @, &, or #. The function of these symbols is not relevant to this parsing process, but there will always be one of them. The string itself can be almost any mathematical expression, and can include constants. Here are a few real-world examples:

L@*K@*H@/324
(AF#-AR#)*(Y#+BB#)
TT#*(L#+D@)*C@
((AE#-AR#)*(Y#+A#))/Y#

The reason I need to replace certain variables is that, when importing these formula strings from one database to another, their variables may not represent the same thing. I have a means of establishing the replacement variable; in other words, I'm able to determine that 'L' needs to be replaced with 'AT'. It's the actual replacement that's an issue.

I hope that clarifies the problem.

Thanks,
Ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28111 Visits: 39939
ok here's a quick prototype i *think* does what you are asking;
it's basically a power-replace that cross joins your funciton symbols with a know list of repalces;
does this work the way you expect?


with MyTable(ID,SomeString)
AS
(
SELECT 1,'L@*K@*H@/324' UNION ALL
SELECT 2,'(AF#-AR#)*(Y#+BB#)' UNION ALL
SELECT 3,'TT#*(L#+D@)*C@' UNION ALL
SELECT 4,'((AE#-AR#)*(Y#+A#))/Y#/'
),
MyFn (fnval) AS
(
SELECT '@' UNION ALL
SELECT '&' UNION ALL
SELECT '#'
),
MyReplaceMents (oldval,newval)
AS
(
SELECT 'L','AT'
)

select
MyTable.* ,
REPLACE(SomeString,oldval + MyFn.fnval ,newval + MyFn.fnval ) AS NEWVAL
FROM MyTable
CROSS JOIN MyFn
CROSS JOIN MyReplaceMents




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 996
Lowell (9/7/2012)
does this work the way you expect?


Well... it certainly could be a step in the right direction, but I'm not quite there yet. Here's the result set I'm getting:


ID SomeString NEWVAL
--------------------------------------------------
1 L@*K@*H@/324 AT@*K@*H@/324
2 (AF#-AR#)*(Y#+BB#) (AF#-AR#)*(Y#+BB#)
3 TT#*(L#+D@)*C@ TT#*(L#+D@)*C@
4 ((AE#-AR#)*(Y#+A#))/Y# ((AE#-AR#)*(Y#+A#))/Y#
1 L@*K@*H@/324 L@*K@*H@/324
2 (AF#-AR#)*(Y#+BB#) (AF#-AR#)*(Y#+BB#)
3 TT#*(L#+D@)*C@ TT#*(L#+D@)*C@
4 ((AE#-AR#)*(Y#+A#))/Y# ((AE#-AR#)*(Y#+A#))/Y#
1 L@*K@*H@/324 L@*K@*H@/324
2 (AF#-AR#)*(Y#+BB#) (AF#-AR#)*(Y#+BB#)
3 TT#*(L#+D@)*C@ TT#*(AT#+D@)*C@
4 ((AE#-AR#)*(Y#+A#))/Y# ((AE#-AR#)*(Y#+A#))/Y#



The bad news is that there are twelve rows instead of four. The good news is that one of those three copies contains the replaced variable. So the bad news is easily eliminated by altering the SELECT and making it into an additional CTE:


,cte_Replace AS
(select
MyTable.* ,
REPLACE(SomeString,oldval + MyFn.fnval ,newval + MyFn.fnval ) AS NEWVAL
FROM MyTable
CROSS JOIN MyFn
CROSS JOIN MyReplaceMents)
SELECT * FROM cte_Replace
WHERE SomeString != NEWVAL



This excludes records where no variables were replaced, but that's fine. They can be discarded and it won't affect my overall task.

The first problem is that if I try to do more than one replacement in the same pass:


MyReplaceMents (oldval,newval)
AS
(
SELECT 'L','AT'
UNION
SELECT 'AR','ZZ'
),



...any row that contains two replacements appears twice in the result set, once for each replacement, and each row containing only one of the replacements. But I suppose that could be resolved with multiple iterations. That would certainly be less painful than iterating through each formula string character-by-character.

The larger problem is that when I change the second formula to '(AL#-AR#)*(Y#+BB#)' to test the "do not replace" condition, the AL# becomes AAT#. So it's not discriminating based on a preceding character, which is the primary requirement. Without it, we basically have the same results as if we'd used a simple REPLACE() function.

The more I consider this, the less convinced I am that there's a solution to this that involves REPLACE(). But I haven't given up hope yet.

Thank you very much for your efforts, Lowell. I'll dig more into your suggestion and see if there's a tweak that can be made to overcome the "do not replace" problem.

Thanks!
Ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16466 Visits: 19086
Am I overthinking?
EDIT: No, I'm undertesting.


DECLARE @Searched char(1),
@New char(1)
SET @Searched = 'L'
SET @New = 'M'
DECLARE @Test TABLE(
string varchar(1000))

INSERT @Test
SELECT 'L@*K@*H@/324' AS string
UNION ALL SELECT '(AF#-AR#)*(Y#+BB#)'
UNION ALL SELECT 'TT#*(L#+D@)*L@'
UNION ALL SELECT '((AE#-AR#)*(Y#+A#))/Y#';

WITH cteTally(n) AS ( SELECT TOP 1000 ROW_NUMBER() OVER( Order BY (SELECT NULL)) FROM master.sys.all_columns),
Data AS(
SELECT '+' + string string, n, SUBSTRING( '+' + string, n, 1) ch
FROM @Test, cteTally)
UPDATE t SET string = STUFF( REPLACE( d.string, dp.ch+d.ch+dn.ch, dp.ch+@New+dn.ch),1,1,'')
FROM @Test t
JOIN Data d ON '+' + t.string = d.string
JOIN Data dp ON d.string = dp.string
AND d.n-1 = dp.n
AND dp.ch LIKE '[^A-Z]'
JOIN Data dn ON d.string = dn.string
AND d.n+1 = dn.n
AND dn.ch IN ( '#', '@', '&')
WHERE d.ch = @Searched

SELECT * FROM @Test




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search