May 7, 2012 at 1:40 pm
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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