I'll try to make this simple, but first, a few requests & info for you:
1) Do not ask the "why", just help me with my question, how to change my SQL
2) I will try to explain this in as simple terms and with enough info that I think is needed for this post
3) Do not tell me this should be done more easily in an OOP language..I have already realized this but, I don’t have time to turn back now.I want to use what I have that works, and simply modify it for now to get the results I need with your help
4) I am not using SQL 2005 so I cannot put in nice regex functions.I realized now, I should have used C# for this *** but oh well, too late to turn back, time is of the essence
5) Yes, it’s the stupidest thing, we have ProductIDs in a memo field and we’re now fixing this by inserting these ProductIDs returned back into a bridge table.I was not on this team, I would never design a table like this in the first place, but…with that said, I am assigned to fix the data.So please refrain from any remarks to that…I hear ya!I hate it too.
6) Do not assume that I know SQL well.I do know pretty advanced, but not this extreme, this is getting crazy with all these PATINDEX, CHARINDEX, and in combination/use with SUBSTRING.I am first and foremost a C# programmer, not a SQL guru by all means.
First off, the existing function works great.It takes in a ProductDescription that has ProductIDs embedded in it (again, yes, very stupid) and then this function returns a nice comma deilimited string of ProductIDs like this:
106761,106763,106791,105813
My goal, and what’s hard for me:
1) I realized I need to change this function to return ProductIDs based on product type: WAV vs. MP3
2) I need help with modifying my existing function above, changing the Substring, PatIndex, or whatever so that it checks the current ProductDescription, looks for some strings that tell you whether it’s a WAV or ProductID, and rips out WAV or MP3 productIDs specifically.
Other Information you need:
There are 2 posssible types of @.ProductDescription formats that could be passed to this function.
The # of ProductIDs vary and this function already handles that problem
Example 1:
‘These music tracks invoke the spirit<br><br><span class='product-name-no-link'>You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>’
Example 2:
‘Clapping percussion effects characterize this Hip Hop/Urban piece with train sound effects and strings.<br><br><b>Styles:</b> Dramatic,Reflective,Somber/dark<br><br><b>If you like this track, you can save more than <span style='color=#ff0000'>70%</span> by purchasing it on the following albums:</b><br><a href="ProductInfo.aspx?ProductID=106758">Hip-Hop / Urban</a><br><a href="ProductInfo.aspx?ProductID=106763">Documentary, Film, Television, General Production - Volume 2</a><br><br>Click <a href="ProductInfo.aspx?ProductID=105747">here</a> for the WAV version of this track.’
Conclusion
1) So basically I assume the best way to do this would be to somehow check on the following strings in order to determine what ProductIDs to bring back in the @.result:
‘MP3</a>’
‘WAV</a>’
‘for the WAV version of this track’
‘for the MP3 version of this track’
Existing Script:
ALTER FUNCTION [dbo].[GetProductChildIDs] (
@.ProductDescription varchar(5500),
@.FileFormatvarchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE@.Location varchar(50),
@.Result varchar(1000)
SET @.Result = ''
SET @.Location = PATINDEX('%ProductID=%',@.ProductDescription)+10
WHILE @.Location > 10
BEGIN
SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription,PATINDEX('%ProductID=%',@.ProductDescription)+10, (CHARINDEX('"',@.ProductDescription,PATINDEX('%ProductID=%',@.ProductDescription)) - (PATINDEX('%ProductID=%',@.ProductDescription)+10))) + ','
SET @.ProductDescription = RIGHT(@.ProductDescription,LEN(@.ProductDescription) - @.Location)
SET @.Location = PATINDEX('%ProductID=%',@.ProductDescription)+10
END
-- Return the comma delimited string of child ProductIDs for Currrent ProductID
-- and get rid of the last comma
return substring(@.Result,1,len(@.Result)-1)
END
My start, a mock-up:
Here’s a mock-up of trying to start this.
ALTER FUNCTION [dbo].[GetProductChildIDs] (
@.ProductDescription varchar(5500),
@.FileFormatvarchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE@.Location varchar(50),
@.Result varchar(1000)
SET @.Result = ''
If @.FileFormat = 'MP3'
BEGIN
WHILE @.Location > 10
BEGIN
Get and set only the ProductIDs for MP3
END
-- Return the comma delimited string of MP3 ProductIDs
return substring(@.Result,1,len(@.Result)-1)
END
If @.FileFormat = 'WAV'
BEGIN
WHILE @.Location > 10
BEGIN
Get and set only the ProductIDs for WAV
END
-- Return the comma delimited string of WAV ProductIDs
return substring(@.Result,1,len(@.Result)-1)
END
END
Looking at the first example first...
I think if you run some sort of string split function (like Itzik's at http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt - don't worry, you don't need SQL2005 for it, just be prepared to make yourself an auxiliary table of numbers), then you could split your string on "<BR>" to break it into the lines for each song.
Then you could split those lines on "ProductID=", to get something that looks like:
1.1 - 01. American Plains <a
href="ProductInfo.aspx?
1.2 - 105234">MP3</a> | <a href="ProductInfo.aspx?
1.3 - 105235">WAV</a>2.1 - 02. Sultry Summer Night <a href="ProductInfo.aspx?
2.2 - 105236">MP3</a> | <a href="ProductInfo.aspx?
2.3 - 105237">WAV</a>3.1 - 03. Ocean Skyline <a href="ProductInfo.aspx?
3.2 - 105238">MP3</a> | <a href="ProductInfo.aspx?
3.3 - 105239">WAV</a>etc.
So then you should be able to quite easily pull out the song title, ID and type.
My code is here... the stuff in the comment at the start sets it up, and then the rest is to pull the data out. I haven't tried to do the second format... but I'm sure you can look at what I'm doing here and adapt it accordingly.
/*
create table dbo.Nums (n int primary key)
while (select count(*) from nums) < 1000
insert into nums select n + (select count(*) from nums) from nums
create table largetext (id int identity(1,1) primary key, largetext nvarchar(3000))
insert into largetext values (
'‘These music tracks invoke the spirit<br><br><span class=''product-name-no-link''>You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>’')
select * from largetext
*/
declare @.rowseparator nvarchar(100)
set @.rowseparator = N'<br>'
SELECT
l.id,
(n - LEN(REPLACE(LEFT(l.largetext, n), @.rowseparator, ''))) / len(@.rowseparator) + 1 AS pos,
SUBSTRING(l.largetext, n,
CHARINDEX(@.rowseparator, l.largetext + @.rowseparator, n) - n)
AS val
into #lines
FROM dbo.largetext l JOIN dbo.Nums
ON n <= LEN(l.largetext)
AND SUBSTRING(@.rowseparator + l.largetext, n, len(@.rowseparator)) = @.rowseparator
set @.rowseparator = N'ProductID='
SELECT
l.id,
l.pos as lineid,
(n - LEN(REPLACE(LEFT(l.val, n), @.rowseparator, ''))) / len(@.rowseparator) + 1 AS pos,
SUBSTRING(l.val, n,
CHARINDEX(@.rowseparator, l.val + @.rowseparator, n) - n)
AS val
into #lines2
FROM #lines l JOIN dbo.Nums
ON n <= LEN(l.val)
AND SUBSTRING(@.rowseparator + l.val, n, len(@.rowseparator)) = @.rowseparator
where l.val like '%' + @.rowseparator + '%'
select left(titles.val,charindex(N'<a href',titles.val)-2) as title,
left(pid.val,charindex(N'"',pid.val)-1) as productid,
substring(pid.val,charindex(N'>',pid.val)+1,charindex(N'</a>',pid.val)-charindex(N'>',pid.val)-1) as producttype
from #lines2 titles
join
#lines2 pid
on titles.lineid = pid.lineid
and titles.pos = 1
and pid.pos > 1
drop table #lines
drop table #lines2|||
Rob, thank you, and of course I will work on this and take maybe a different approach. I just get head spins sometimes with combining the Substring, Charindex, and Patindex together when it's getting this complex.
Thanks a lot, I'll try it from here and also make sure I understand it going forward.
Also, checked out your blog, nice! I see you're from Austrailia. My experience with people from Britain and Austrailia is that they are very pleasant people. I wish in general, programmers in the US could be more like you...instead of ego trips like I see every day here for the past 10 years in my profession with many of them.
cheers!
|||The principle behind this approach is to join it to a table of numbers. That way, you can easily analyse what's in each character. If it's not the start of the separator, then you can tell the system to ignore it.It's very different to the way you'd do it in C#, because it's taking a set-based approach to the problem - not stepping through it bit by bit. If you insert extra rows into that largetext table, you'll see that it doesn't really take much longer.
And please don't call me 'Bob'... No-one calls me that.
Rob|||Thank you for explaining. I corrected the Rob.
...anyway, is there any other way? Because actually i have to add the id from 1st table to many other tables...maybe 5 6 tables...i think a lot of sql query i have to execute if i have to add so many ids from 1st table...