Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Tricky REPLACE problem Expand / Collapse
Author
Message
Posted Friday, September 7, 2012 1:25 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 846, Visits: 850
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
Post #1356211
Posted Friday, September 7, 2012 1:47 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:44 AM
Points: 1,293, Visits: 1,427
replace the untouchable 'BA#' first, then do the main replace, then put the 'BA#' back

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

Post #1356219
Posted Friday, September 7, 2012 1:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 12,881, Visits: 31,815
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
Post #1356221
Posted Friday, September 7, 2012 1:52 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 846, Visits: 850
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
Post #1356223
Posted Friday, September 7, 2012 1:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 3,342, Visits: 7,213
Another suggestion.
SELECT STUFF( REPLACE( '+' + string, '+A#', '+C#'), 1, 1, '')
FROM(SELECT 'A#+AB#+BA#+B#' AS string) A




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1356224
Posted Friday, September 7, 2012 1:58 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 3,342, Visits: 7,213
@Lowell
Shouldn't the splitting be done on the plus sign(+)? These look as adding formulas.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1356227
Posted Friday, September 7, 2012 2:01 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 846, Visits: 850
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
Post #1356231
Posted Friday, September 7, 2012 2:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 12,881, Visits: 31,815
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
Post #1356234
Posted Friday, September 7, 2012 2:39 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 846, Visits: 850
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
Post #1356250
Posted Friday, September 7, 2012 2:43 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 3,342, Visits: 7,213
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1356251
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse