Post reply

Updating text within a single column, but not the entire string, duplicates string being updated

  • May 7, 2012 at 1:40 pm

    #256081

    Apologies for post this issue again, but I am tacking it from various angles and logging new post as I find new issues (while moving forward with the work)

    My issue very similar to http://qa.sqlservercentral.com/Forums/Topic1012573-392-1.aspx, but in my case, there could be more than one paragraph in a column...

    I'm going to make this as simplified as possible...

    These are the contents of a sample column

    <body><p>Link with only valid <a macro_name="productLink" code1="1055288" href="" macro_uid="_13360741834971941">code1 </a>sku</p>

    <p>Link with only valid <a macro_name="productLink" macro_uid_13360741834942686" href="" macro_uid="_13360741834942686" code2="01F8179">code2 </a>sku</p>

    <p>Link with <a macro_name="productLink" macro_uid_1336074183491872" code1="1055355" href="" macro_uid="_1336074183491872" code2="01F8179">both valid </a>code1 and code2 Skus</p>

    I have come up the following query....

    declare @myvar varchar(max)

    declare @myvartmp varchar(max)

    declare @code2sku varchar(50)

    declare @code2sku varchar(50)

    declare @prereplace varchar(50)

    declare @postreplace varchar(50)

    declare @inposcode1 int

    declare @poscode1 int

    declare @poscode2 int

    declare @poscode1tmp int

    declare @poslsqbrkt int

    declare @posrsqbrkt int

    declare @posrsqbrkttmp int

    declare @counter int

    set @myvar = ''

    set @myvartmp = ''

    set @counter = '0'

    declare c1 cursor

    for

    select bodytext from documentbody where bodyid = '236786'

    open c1

    fetch next from c1 into @myvar

    while @@fetch_status = 0

    begin

    set @myvartmp = @myvar

    set @poslsqbrkt = (select charindex('<a __jive_macro_name',@myvar))

    set @posrsqbrkt = (select charindex('">',@myvar,@poslsqbrkt))

    set @poscode1 = (select charindex('code1="', @myvar, @poslsqbrkt))

    set @poscode2 = (select charindex('code2="',@myvar,@poslsqbrkt))

    if (@poscode1 between @poslsqbrkt and @posrsqbrkt) and (@poscode2 not between @poslsqbrkt and @posrsqbrkt)

    begin

    set @counter = @counter + 1

    set @code2sku = (select substring(@myvar, @poscode1 + 12,7))

    set @code2sku = (select max(code2_part_sku) from LinkSKU where code1_part_sku = @code2sku)

    if @code2sku <> ''

    begin

    select 'update: ' + cast(@counter as varchar)

    select @myvartmp

    select 'leftsquarebracket: ' + cast(@poslsqbrkt as varchar)

    select 'code1: ' + cast(@poscode1 as varchar)

    select 'code2 :' + cast(@poscode2 as varchar)

    select 'rightquarebracket: ' + cast(@posrsqbrkt as varchar)

    set @prereplace = 'code1="'+@code2sku+'"'

    set @postreplace = @prereplace+' code2="'+@code2sku+'"'

    set @postreplace = ' code2="'+@code2sku+'" ' + @prereplace

    set @myvar = replace(@myvar, @prereplace,@postreplace)

    select 'before update: ' + @prereplace

    select 'after update: ' + @postreplace

    update documentbody

    set bodytext = @myvar

    where current of c1

    end

    end

    while (@poslsqbrkt <> '0')

    begin

    set @myvar = @myvartmp

    set @posrsqbrkttmp = @posrsqbrkt

    set @poscode1 = ''

    set @poscode2 = ''

    set @poslsqbrkt = ''

    set @posrsqbrkt = ''

    set @poslsqbrkt = (select charindex('<a macro_name',@myvar,@posrsqbrkttmp))

    set @posrsqbrkt = (select charindex('">',@myvar,@poslsqbrkt))

    set @poscode1 = (select charindex('code1="', @myvar, @poslsqbrkt))

    set @poscode2 = (select charindex('code2="',@myvar,@poslsqbrkt))

    if (@poscode1 between @poslsqbrkt and @posrsqbrkt) and (@poscode2 not between @poslsqbrkt and @posrsqbrkt)

    begin

    set @counter = @counter + 1

    set @code2sku = (select substring(@myvar, @poscode1 + 12,7))

    set @code2sku = (select max(pf_part_sku) from pfAutoLinkSKU where nw_part_sku = @code2sku)

    if @code2sku <> ''

    begin

    select 'update: ' + cast(@counter as varchar)

    set @prereplace = 'code1="'+@code2sku+'"'

    set @postreplace = @prereplace+' code2="'+@code2sku+'"'

    set @postreplace = ' code2="'+@code2sku+'" ' + @prereplace

    set @myvar = replace(@myvar, @prereplace,@postreplace)

    select 'before update: ' + @prereplace

    select 'after update: ' + @postreplace

    update documentbody

    set bodytext = @myvar

    where current of c1

    end

    end

    end

    fetch next from c1 into @myvar

    end

    close c1

    deallocate c1

    I hope my query is not to convoluted to read..

    Essentially this query does what I need it to do, except that if it finds a code2="01F8179", it updates every occurence of it, rather than just the one which is missing a code1.

    Thanks.

  • May 7, 2012 at 1:50 pm

    #1484208

    Isn't this just a continuation of your thread from a couple months ago? It does not look like we ever found a resolution for that one.

    http://qa.sqlservercentral.com/Forums/Topic1255283-145-1.aspx

    How about we get some ddl, sample data and desired output? That will help make this clear what you are trying to do. Otherwise we are just shooting in the dark.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 7, 2012 at 2:06 pm

    #1484219

    Sean Lange (5/7/2012)

    Isn't this just a continuation of your thread from a couple months ago? It does not look like we ever found a resolution for that one.

    http://qa.sqlservercentral.com/Forums/Topic1255283-145-1.aspx

    How about we get some ddl, sample data and desired output? That will help make this clear what you are trying to do. Otherwise we are just shooting in the dark.

    Hi Sean,

    it is, but I have figured out a way forward since then, though I am now stuck at one last point. The ddl and sample data I posted in the thread you referenced is still valid, if you don't mind using that.

    My desired output...

    Every paragraph where either code1 or code2 exists( and they are valid), should be updated with the corresponding code2 or code1

    So the following...

    <p>Link with only valid <a macro_name="productLink" code1="1055288" href="" macro_uid="_13360741834971941">code1 </a>sku</p>

    <p>Link with only valid <a macro_name="productLink" macro_uid_13360741834942686" href="" macro_uid="_13360741834942686" code2="01F8179">code2 </a>sku</p>

    should end up like this...

    <p>Link with only valid <a macro_name="productLink" code1="1055288" code2="1B5B288"href="" macro_uid="_13360741834971941">code1 </a>sku</p>

    <p>Link with only valid <a macro_name="productLink" macro_uid_13360741834942686" href="" macro_uid="_13360741834942686" code1="1033288" code2="01F8179">code2 </a>sku</p>

    Any Paragraph in which code1 and code2 already exist...

    <p>Link with only valid <a macro_name="productLink" macro_uid_13360741834942686" href="" macro_uid="_13360741834942686" code1="1033288" code2="01F8179">code2 </a>sku</p>

    should not be updated.

    In the last iteration of my query, I had the following in the column...

    <body><p>Link with only valid <a macro_name="productLink" code1="1055288" href="" macro_uid="_13360741834971941">code1 </a>sku</p>

    <p>Link with only valid <a macro_name="productLink" macro_uid_13360741834942686" href="" macro_uid="_13360741834942686" code2="01F8179">code2 </a>sku</p>

    <p>Link with <a macro_name="productLink" macro_uid_1336074183491872" code1="1055355" href="" macro_uid="_1336074183491872" code2="01F8179">both valid </a>code1 and code2 Skus</p>

    Both of this paragraph was correctly updated

    <p>Link with only valid <a macro_name="productLink" macro_uid_13360741834942686" href="" macro_uid="_13360741834942686" code2="01F8179">code2 </a>sku</p>

    ,

    But this was updated as well..

    <p>Link with <a macro_name="productLink" macro_uid_1336074183491872" code1="1055355" href="" macro_uid="_1336074183491872" code2="01F8179">both valid </a>code1 and code2 Skus</p>

    According to my logic, it should not have been updated.

    Let me know if you need me to repost the ddl and sample data.

    Thanks.

  • May 7, 2012 at 2:17 pm

    #1484226

    The ddl and sample data I posted in the thread you referenced is still valid, if you don't mind using that.

    How about since you never actually posted a nice clean set of ddl and sample data you put it together? I really don't want to parse through a bunch of stuff scattered through multiple posts and try it put this all together again. It will benefit your answers greatly if you can make your samples and data as clean as possible.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 7, 2012 at 2:19 pm

    #1484228

    Sean Lange (5/7/2012)

    The ddl and sample data I posted in the thread you referenced is still valid, if you don't mind using that.

    How about since you never actually posted a nice clean set of ddl and sample data you put it together? I really don't want to parse through a bunch of stuff scattered through multiple posts and try it put this all together again. It will benefit your answers greatly if you can make your samples and data as clean as possible.

    Thanks Sean, will do shortly.

  • May 7, 2012 at 3:02 pm

    #1484247

    rabisco (5/7/2012)

    Sean Lange (5/7/2012)

    The ddl and sample data I posted in the thread you referenced is still valid, if you don't mind using that.

    How about since you never actually posted a nice clean set of ddl and sample data you put it together? I really don't want to parse through a bunch of stuff scattered through multiple posts and try it put this all together again. It will benefit your answers greatly if you can make your samples and data as clean as possible.

    Thanks Sean, will do shortly.

    DDL and sample data is below...

    CREATE TABLE [dbo].[Linktable](

    [id] [bigint] IDENTITY(1,1) NOT NULL,

    [part_ref] [bigint] NOT NULL,

    [code1_sku] [varchar](255) NULL,

    [code2_sku] [varchar](255) NULL)

    go

    insert Linktable

    SELECT 11259,'1595501','83K3087' UNION ALL

    SELECT 11260,'1595474','83K3074' UNION ALL

    SELECT 11261,'1727495','66F9349' UNION ALL

    SELECT 11262,'1727584','66F9341' UNION ALL

    SELECT 11263,'1728200','66F9331' UNION ALL

    SELECT 11264,'1729014','66F9330' UNION ALL

    SELECT 11265,'1729156','66F9347' UNION ALL

    SELECT 11266,'1730135','66F9343' UNION ALL

    SELECT 11267,'1730185','66F9333' UNION ALL

    SELECT 11268,'1730523','66F9342' UNION ALL

    SELECT 11269,'1732360','66F9334' UNION ALL

    SELECT 11270,'1732364','66F9344' UNION ALL

    SELECT 11271,'1732413','66F9335' UNION ALL

    SELECT 11272,'1732423','66F9345' UNION ALL

    SELECT 11273,'1741637','02F6689' UNION ALL

    SELECT 11274,'1741646','02F6675' UNION ALL

    SELECT 11275,'1741660','02F6727' UNION ALL

    SELECT 11276,'1741667','02F6732' UNION ALL

    SELECT 11277,'1741673','02F6728' UNION ALL

    SELECT 11278,'1741675','02F6763'

    go

    CREATE TABLE [dbo].[documentBody](

    [bodyID] [bigint] NOT NULL,

    [bodyText] varchar(max) NULL)

    go

    insert documentBody

    SELECT 1000, null UNION ALL

    SELECT 1001, null UNION ALL

    SELECT 1002, null UNION ALL

    SELECT 1003, null UNION ALL

    SELECT 1004, null UNION ALL

    SELECT 1005, null UNION ALL

    SELECT 1006, null UNION ALL

    SELECT 1007, '<body>

    <a name="productLink" class="productLink" code1="1595501" href="">sku1</a></body>' UNION ALL

    SELECT 1008, '<body>

    <a name="productLink" class="productLink" code2="83K3074" href="">sku2</a></body>' UNION ALL

    SELECT 1009, '<body>

    <a name="productLink" class="productLink code1="1595501" href="" uid="13298529604731501" code2="83K3087" >sku3</a>

    <a name="productLink" class="productLink" code2="66F9331" href="">sku4</a></body>' UNION ALL

    SELECT 1010, '<body>

    <a name="productLink" class="productLink code1="1111111" href="" uid="13298535612357259">sku5</a>

    <a name="productLink" class="productLink" code1="1111111" href="" code2=code1="11B11B1">sku6</a></body>'

    I hope what I'm trying to achieve is clearer now.

    Thanks.

  • May 8, 2012 at 10:27 am

    #1484649

    rabisco (5/7/2012)

    rabisco (5/7/2012)

    Sean Lange (5/7/2012)

    The ddl and sample data I posted in the thread you referenced is still valid, if you don't mind using that.

    How about since you never actually posted a nice clean set of ddl and sample data you put it together? I really don't want to parse through a bunch of stuff scattered through multiple posts and try it put this all together again. It will benefit your answers greatly if you can make your samples and data as clean as possible.

    Thanks Sean, will do shortly.

    DDL and sample data is below...

    CREATE TABLE [dbo].[Linktable](

    [id] [bigint] IDENTITY(1,1) NOT NULL,

    [part_ref] [bigint] NOT NULL,

    [code1_sku] [varchar](255) NULL,

    [code2_sku] [varchar](255) NULL)

    go

    insert Linktable

    SELECT 11259,'1595501','83K3087' UNION ALL

    SELECT 11260,'1595474','83K3074' UNION ALL

    SELECT 11261,'1727495','66F9349' UNION ALL

    SELECT 11262,'1727584','66F9341' UNION ALL

    SELECT 11263,'1728200','66F9331' UNION ALL

    SELECT 11264,'1729014','66F9330' UNION ALL

    SELECT 11265,'1729156','66F9347' UNION ALL

    SELECT 11266,'1730135','66F9343' UNION ALL

    SELECT 11267,'1730185','66F9333' UNION ALL

    SELECT 11268,'1730523','66F9342' UNION ALL

    SELECT 11269,'1732360','66F9334' UNION ALL

    SELECT 11270,'1732364','66F9344' UNION ALL

    SELECT 11271,'1732413','66F9335' UNION ALL

    SELECT 11272,'1732423','66F9345' UNION ALL

    SELECT 11273,'1741637','02F6689' UNION ALL

    SELECT 11274,'1741646','02F6675' UNION ALL

    SELECT 11275,'1741660','02F6727' UNION ALL

    SELECT 11276,'1741667','02F6732' UNION ALL

    SELECT 11277,'1741673','02F6728' UNION ALL

    SELECT 11278,'1741675','02F6763'

    go

    CREATE TABLE [dbo].[documentBody](

    [bodyID] [bigint] NOT NULL,

    [bodyText] varchar(max) NULL)

    go

    insert documentBody

    SELECT 1000, null UNION ALL

    SELECT 1001, null UNION ALL

    SELECT 1002, null UNION ALL

    SELECT 1003, null UNION ALL

    SELECT 1004, null UNION ALL

    SELECT 1005, null UNION ALL

    SELECT 1006, null UNION ALL

    SELECT 1007, '<body>

    <a name="productLink" class="productLink" code1="1595501" href="">sku1</a></body>' UNION ALL

    SELECT 1008, '<body>

    <a name="productLink" class="productLink" code2="83K3074" href="">sku2</a></body>' UNION ALL

    SELECT 1009, '<body>

    <a name="productLink" class="productLink code1="1595501" href="" uid="13298529604731501" code2="83K3087" >sku3</a>

    <a name="productLink" class="productLink" code2="66F9331" href="">sku4</a></body>' UNION ALL

    SELECT 1010, '<body>

    <a name="productLink" class="productLink code1="1111111" href="" uid="13298535612357259">sku5</a>

    <a name="productLink" class="productLink" code1="1111111" href="" code2=code1="11B11B1">sku6</a></body>'

    I hope what I'm trying to achieve is clearer now.

    Thanks.

    Hi Sean,

    was the ddl and sample data I posted useful?

    Thanks.

  • May 8, 2012 at 10:38 am

    #1484657

    rabisco (5/8/2012)

    Hi Sean,

    was the ddl and sample data I posted useful?

    Thanks.

    Well it certainly demonstrated the problem. Is this a one time thing or something you need to run regularly? If this sample data is representative of your actual data you have a serious nightmare on your hands.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 8, 2012 at 10:44 am

    #1484659

    Sean Lange (5/8/2012)

    rabisco (5/8/2012)

    Hi Sean,

    was the ddl and sample data I posted useful?

    Thanks.

    Well it certainly demonstrated the problem. Is this a one time thing or something you need to run regularly? If this sample data is representative of your actual data you have a serious nightmare on your hands.

    Hi Sean, this is something that would need to be run regularly. The sample data is representative of the actual data. With bated breath, I ask, "so what am I up against here"?

    Thanks.

  • May 8, 2012 at 10:53 am

    #1484665

    rabisco (5/8/2012)

    Sean Lange (5/8/2012)

    rabisco (5/8/2012)

    Hi Sean,

    was the ddl and sample data I posted useful?

    Thanks.

    Well it certainly demonstrated the problem. Is this a one time thing or something you need to run regularly? If this sample data is representative of your actual data you have a serious nightmare on your hands.

    Hi Sean, this is something that would need to be run regularly. The sample data is representative of the actual data. With bated breath, I ask, "so what am I up against here"?

    Thanks.

    This is going to be really challenging. Take this portion of one your example rows.

    <a name="productLink" class="productLink" code1="1111111" href="" code2=code1="11B11B1">sku6</a></body>

    What are you going to do with that? There are two values for code1 and code2 is a mess. What should this one look like? I don't see either value of code1 in your Linktable sample data and there is nothing useful at all about code2.

    Add to this that you in the second set of replacement block in this already. :w00t:

    How much data is in the real tables? 1,000 rows? 10,000,000? I can't even begin to come up with a way to automate straightening this out accurately.

    Any chance you go back to the person(s) that entered this gibberish and tell them to fix it? 😛

    I will try to give this some thought and see what I can come up. In the meantime, let's see if anybody else might come up with something.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 8, 2012 at 10:59 am

    #1484669

    Sean Lange (5/8/2012)

    rabisco (5/8/2012)

    Sean Lange (5/8/2012)

    rabisco (5/8/2012)

    Hi Sean,

    was the ddl and sample data I posted useful?

    Thanks.

    Well it certainly demonstrated the problem. Is this a one time thing or something you need to run regularly? If this sample data is representative of your actual data you have a serious nightmare on your hands.

    Hi Sean, this is something that would need to be run regularly. The sample data is representative of the actual data. With bated breath, I ask, "so what am I up against here"?

    Thanks.

    This is going to be really challenging. Take this portion of one your example rows.

    <a name="productLink" class="productLink" code1="1111111" href="" code2=code1="11B11B1">sku6</a></body>

    What are you going to do with that? There are two values for code1 and code2 is a mess. What should this one look like? I don't see either value of code1 in your Linktable sample data and there is nothing useful at all about code2.

    Add to this that you in the second set of replacement block in this already. :w00t:

    How much data is in the real tables? 1,000 rows? 10,000,000? I can't even begin to come up with a way to automate straightening this out accurately.

    Any chance you go back to the person(s) that entered this gibberish and tell them to fix it? 😛

    I will try to give this some thought and see what I can come up. In the meantime, let's see if anybody else might come up with something.

    Hi Sean,

    I hold my head in shame.

    The last row in the sample data should read ....

    <a name="productLink" class="productLink" code1="1111111" href="" code2="11B11B1">sku6</a></body>'

    and should not get updated since there no data for it in the link table. It is an example of invalid data. Apologies for the confusion.

    Thanks.

  • May 9, 2012 at 3:18 pm

    #1485372

    rabisco (5/8/2012)

    Sean Lange (5/8/2012)

    rabisco (5/8/2012)

    Sean Lange (5/8/2012)

    rabisco (5/8/2012)

    Hi Sean,

    was the ddl and sample data I posted useful?

    Thanks.

    Well it certainly demonstrated the problem. Is this a one time thing or something you need to run regularly? If this sample data is representative of your actual data you have a serious nightmare on your hands.

    Hi Sean, this is something that would need to be run regularly. The sample data is representative of the actual data. With bated breath, I ask, "so what am I up against here"?

    Thanks.

    This is going to be really challenging. Take this portion of one your example rows.

    <a name="productLink" class="productLink" code1="1111111" href="" code2=code1="11B11B1">sku6</a></body>

    What are you going to do with that? There are two values for code1 and code2 is a mess. What should this one look like? I don't see either value of code1 in your Linktable sample data and there is nothing useful at all about code2.

    Add to this that you in the second set of replacement block in this already. :w00t:

    How much data is in the real tables? 1,000 rows? 10,000,000? I can't even begin to come up with a way to automate straightening this out accurately.

    Any chance you go back to the person(s) that entered this gibberish and tell them to fix it? 😛

    I will try to give this some thought and see what I can come up. In the meantime, let's see if anybody else might come up with something.

    Hi Sean,

    I hold my head in shame.

    The last row in the sample data should read ....

    <a name="productLink" class="productLink" code1="1111111" href="" code2="11B11B1">sku6</a></body>'

    and should not get updated since there no data for it in the link table. It is an example of invalid data. Apologies for the confusion.

    Thanks.

    Hi Sean,

    did the correction to my sample data make the issue any clearer?

    Thanks.

  • May 15, 2012 at 9:19 am

    #1487808

    rabisco (5/9/2012)

    rabisco (5/8/2012)

    Sean Lange (5/8/2012)

    rabisco (5/8/2012)

    Sean Lange (5/8/2012)

    rabisco (5/8/2012)

    Hi Sean,

    was the ddl and sample data I posted useful?

    Thanks.

    Well it certainly demonstrated the problem. Is this a one time thing or something you need to run regularly? If this sample data is representative of your actual data you have a serious nightmare on your hands.

    Hi Sean, this is something that would need to be run regularly. The sample data is representative of the actual data. With bated breath, I ask, "so what am I up against here"?

    Thanks.

    This is going to be really challenging. Take this portion of one your example rows.

    <a name="productLink" class="productLink" code1="1111111" href="" code2=code1="11B11B1">sku6</a></body>

    What are you going to do with that? There are two values for code1 and code2 is a mess. What should this one look like? I don't see either value of code1 in your Linktable sample data and there is nothing useful at all about code2.

    Add to this that you in the second set of replacement block in this already. :w00t:

    How much data is in the real tables? 1,000 rows? 10,000,000? I can't even begin to come up with a way to automate straightening this out accurately.

    Any chance you go back to the person(s) that entered this gibberish and tell them to fix it? 😛

    I will try to give this some thought and see what I can come up. In the meantime, let's see if anybody else might come up with something.

    Hi Sean,

    I hold my head in shame.

    The last row in the sample data should read ....

    <a name="productLink" class="productLink" code1="1111111" href="" code2="11B11B1">sku6</a></body>'

    and should not get updated since there no data for it in the link table. It is an example of invalid data. Apologies for the confusion.

    Thanks.

    Hi Sean,

    did the correction to my sample data make the issue any clearer?

    Thanks.

    Hey Sean, any new insights in this?

  • May 15, 2012 at 10:35 am

    #1487858

    The more I think about this the more ugly it becomes. Can this be done in managed code instead in the database? t-sql is just not that efficient at this type of string manipulation. Maybe even a CLR proc? It would still be kind of ugly but I think better than trying to do this in straight sql.

    Even better would be to have the people entering this to edit their data so it is correct. 😛

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 15, 2012 at 11:18 am

    #1487888

    Sean Lange (5/15/2012)

    The more I think about this the more ugly it becomes. Can this be done in managed code instead in the database? t-sql is just not that efficient at this type of string manipulation. Maybe even a CLR proc? It would still be kind of ugly but I think better than trying to do this in straight sql.

    Even better would be to have the people entering this to edit their data so it is correct. 😛

    Thanks for responding.

    Unfortunately, this is a project to attempt to correct Public user data. The requirement is to correct the incomplete data via a nightly database job.

    Still wearing my positive hat :-), while the PM is breathing down my neck 🙁 , it does look like we are close though, I just need to prevent the double updates. Thanks for your help.

Viewing 15 posts - 1 through 15 (of 61 total)

You must be logged in to reply to this topic. Login to reply