From concept to application...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

From concept to application...

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-22 : 11:26:09

Okay so I've got two sets of codes I've written (stolen). They work in concept, but I'm not sure how to apply them. What I want to do is apply them to whole sets of data, but the way I have it written only works for one peice of data.

Can someone enlighten me, give me a pointer, as to what needs to be done to make the step. I feel like I'm just barely missing something....
-----------------------------
Code 1.
use SKUM
Declare @textXml Xml
Declare @Text Varchar(1000),@delimiter NVARCHAR(5)
Set @Text = 'ncs-aprls-_aim-lh002_20cbl'
set @delimiter = '_'
Select @TextXml = Cast('<d>' + replace(Replace(@Text, '_', '__'), @Delimiter,'</d><d>') + '</d>' As Xml );

select
@text, SUM(items.cost)

from
(
select data from
(
SELECT row_number() over (order by (select 0)) as sno,T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
) as t
where sno%2=1
) broken inner join items on items.ItemNumber like '%' + broken.data + '%'

----------------------------
Code 2

declare @myval varchar(1000)
set @myval='<hi> things will be hidden <i love sql> and thats to be expected <html rulz> when converting html to text';

while ( LEN(@myval) > LEN(replace(@myval,'<','')))

begin
set @myval = (select LEFT(@myval,(case when CHARINDEX('<', @myval)-1 < 0 then 0 else CHARINDEX('<', @myval)-1 end)) + right(@myval, len(@myval)-CHARINDEX('>',@myval)))

continue

end;

select @myval

-----------------------------------------
I want to replace @text/@myval with a column name and have the process run against all entries in the column.

I feel silly asking this question, it seems like its probably pretty simple... but its just NOT working here for me. Help?

---------
p.s.
to work here, @text would be represented by (select productname from EbayDataUsed) and @myval would be (Select DescriptionFields.Description from descriptionfields)

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-22 : 12:07:03

*sigh*, it seems like "THIS" shoud work, but its not.... why?

select
productname
,(select
SUM(items.cost)

from
(
select data from
(
SELECT row_number() over (order by (select 0)) as sno,T.split.value('.', 'nvarchar(max)') AS data
FROM (select Cast('<d>' + replace(Replace(productname, '_', '__'), '_','</d><d>') + '</d>' As Xml ) from EbayDataUsed).nodes('/d') T (split)
) as t
where sno%2=1
) broken inner join items on items.ItemNumber like '%' + broken.data + '%')

from

EbayDataUsed

ConradK
Posting Yak Master

140 Posts

Posted - 2010-06-22 : 12:13:54

maybe I want to build a function.... ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources