|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 662,
Visits: 699
|
|
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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Friday, May 24, 2013 9:02 AM
Points: 1,196,
Visits: 1,320
|
|
replace the untouchable 'BA#' first, then do the main replace, then put the 'BA#' back
SELECT REPLACE(REPLACE(REPLACE(@STRING, 'BA#', '***'), 'A#', 'C#'), '***', 'BA#')
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 11,792,
Visits: 28,082
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 662,
Visits: 699
|
|
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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091,
Visits: 2,205
|
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091,
Visits: 2,205
|
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 662,
Visits: 699
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 11,792,
Visits: 28,082
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 662,
Visits: 699
|
|
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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091,
Visits: 2,205
|
|
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. Please don't trust me, test the solutions I give you before using them. Forum Etiquette: How to post data/code on a forum to get the best help
|
|
|
|