Change function to bring back certain values
-
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 crap 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><b r>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236" >MP3</a> | <a href="ProductInfo.aspx?ProductID=105237" >WAV</a><b r>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238" >MP3</a> | <a href="ProductInfo.aspx?ProductID=105239" >WAV</a><b r>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240" >MP3</a> | <a href="ProductInfo.aspx?ProductID=105241" >WAV</a><b r>’ 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" >Documenta ry, 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),
@FileFormat varchar(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),
@FileFormat varchar(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
-
Okay here goes. If I have understood this correctly:
Looking at the two examples you are going to get one of three scenarios:
1. A "for the WAV version of this track" scenario where upon:
a. The @fileFormat variable is WAV and your function would return ALL of the productIDs.
b. The @fileFormat variable is MP3 and your function would return NONE of the productIDs.
2. A "for the MP3 version of this track" scenario where upon:
a. The @fileFormat variable is MP3 and your function would return ALL of the productIDs.
b. The @fileFormat variable is WAV and your function would return NONE of the productIDs.
3. A mixed bag (example 1) where MP3 and WAVs are together where upon:
a. The @fileFormat variable is MP3 and your function would return MP3 productIDs.
b. The @fileFormat variable is WAV and your function would return WAV productIDs.
Your function should look something like this:
ALTER FUNCTION [dbo].[GetProductChildIDs] (
@ProductDescription varchar(5500),
@FileFormat varchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Location varchar(50),
@Result varchar(1000)
SET @Result = ''
SET @Location = PATINDEX('%ProductID=%',@ProductDescription)+10
-- Both scenarios 1 and 2 can be covered with:
IF (PATINDEX('%for the WAV version of this track%',@ProductDescription) > 0 AND @fileFormat = 'WAV') OR
(PATINDEX('%for the MP3 version of this track%',@ProductDescription) > 0 AND @fileFormat = 'MP3')
BEGIN
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
END
ELSE
-- Scenario 3 covered by:
BEGIN
WHILE @Location > 10
BEGIN
IF PATINDEX('%MP3%', SUBSTRING(@productDescription, PATINDEX('%productID=%',@productDescription)+11, 12)) > 0 AND @fileFormat = 'MP3' OR
PATINDEX('%WAV%', SUBSTRING(@productDescription, PATINDEX('%productID=%',@productDescription)+11, 12)) > 0 AND @fileFormat = 'WAV'
SET @result = @result + LEFT(SUBSTRING(@productDescription, PATINDEX('%productID=%',@productDescription)+11, 12), CHARINDEX('"',SUBSTRING(@productDescription, PATINDEX('%productID=%',@productDescription)+11, 12),1)-1) + ','
SET @ProductDescription = RIGHT(@ProductDescription,LEN(@ProductDescription) - @Location)
SET @Location = PATINDEX('%ProductID=%',@ProductDescription)+10
END
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
Is this what you were after?
-
Horse feathers... asking "WHY" is very often the key to understanding the problem well enough to help you do what you need.
Since I can't ask "WHY" you want to do certain things, guess I'm gonna have to pass on this...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff, why do you bother replying then. And why do you take that so personally. It was to help you and I get to a and b. I was giving YOU background information and telling you what I already know about all this. In the past, people would come back with sarcastic remarks such as "why the hell do you have ProductIDs in a memo field".. or "why don't you just do this in an OOP language"
Why did I say don't ask why? I was specifically referring to any questions that would be asking why we have productIDs in a memo field. That doesn't matter, and I ask that you don't care about that. What does matter is helping me get to a and b is why I said this. We could ramble on the "why" all day and it wouldn't make any bit of difference in this particular post in helping you help me because it's a null point on that question whcih is why I made requests like I did, to save YOU breath. The fact is, I have 2 types of incoming descriptions and I told you to base my help on that along with my existing function, that's it! That's all you need to know to help me figure this out. The "why" is irrelevant. I stated that I did not design the friggin tables. And we're fixing them now. That's also all you need to know. You aren't on my team so I was asking you to skip the "why", it's not worth going into.
I never said you couldn't ask other questions. Read its context, there's no reason for you to act like this if you read this over again. Do I need to put smiley faces to portray what I'm saying to you so that you blow up on me? It's efficient. It's to the point. And you have all the other information you need to help me. If you don't then ask for more info. Just don't ask why we have data coming in like this beause I'm arleady not happy about he fact that our PM or whoever designed this sh*& like this. OK? That's ALL I was saying. I was not tying your hands at all!
I was simply stating information that's useful to you, and what I don't want back from the help so we can save time and not ramble on with stuff that won't make a bit of difference in helping me with my code...so chill man. It's to save you and I both time. If you need schema info fine? stuff like that is fair game, and I expect you to ask for more information for stuff that would help. What I don't like is cocky sob that come back and say stuff like "why the heck would you do this" or "what in the world are you doing". Thats the filter, looks like maybe it worked.
Don't let your ego get in the way of helping people. It was diretion, not dictation. you took it wrong. Take time to see that the person is doing you a favor and understand context, and also another thing, don't assume anything about a person. I have spent hours on this before even posting.
-
Adrian,
Thanks a lot. I guess I have a few qustions.
I don't understand this line:
PATINDEX('%for the WAV version of this track%',@ProductDescription) > 0
so basically I think this is saying ensure that you start at the beginning of ProductDescription right?
Yours looks like it may work. I may need to modify this part
IF (PATINDEX('%for the WAV version of this track%',@ProductDescription) > 0 AND @fileFormat = 'WAV') OR
(PATINDEX('%for the MP3 version of this track%',@ProductDescription) > 0 AND @fileFormat = 'MP3')
to also check for the other type which is '%WAV</a>%' and '%MP3</a>%' strings, not just for '%for the WAV version of this track%' or '%for the MP3 version of this track%'
So I need to try to put an extra OR in each of the checks above.
-
The line PATINDEX('%for the WAV version of this track%',@ProductDescription) > 0 checks for the part of the Product Description that states that all productIDs in the input are for WAV..
The line:
IF (PATINDEX('%for the WAV version of this track%',@ProductDescription) > 0 AND @fileFormat = 'WAV') OR
(PATINDEX('%for the MP3 version of this track%',@ProductDescription) > 0 AND @fileFormat = 'MP3')
... will check if the input to the function is like your example 2. This would then return all of the ProductIDs in that description if the @fileFormat matches the input type "...WAV/MP3 Version of ...."
The ELSE of the statement will return the ProductIDs for MP3s or WAVs contained in the @productDescription like in you example1.
I don't think you'll need to add the OR in each of the checks as those scenarios are covered by the ELSE statement.
-
Thanks Adrian.
But for a description like this incoming, I don't think your check will check for the 'WAV</a>' right?
‘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>’
so I think I stil need to add the check for '%WAV</a>%' since if I pass a description formatted like this, yours is checking only for the other type of description that may have '%for the WAV version of this track%' only.
On another point. I may have more than one '%for the WAV version of this track%' in a description. I can't tell if yours would account for just one at the end or any occurances of this in a description. I assume it does. Let me try it out and maybe I will also modify it to add the check for 'WAV</a>' if those types of descriptions are passed to the function. If you really thnk I don't need the check for 'WAV</a>' , then I guess I still cant' see where that's covered since I only see a check for '%for the WAV version of this track%' . @FileFormat is nothing but a flag to determine what type of product ID to pull & bring back.
thanks! will play with it and try it out or tweak it to work and understand this.
-
re-looking at this, actually it's not going to give me what I want.
I think you are assuming that a description is either all WAVs or all MP3s which is not the case.
If it doesn't explicitely have the strings I mentioned right after the ProductID=, then it's the opposite type
IF (PATINDEX('%for the WAV version of this track%',@ProductDescription) > 0 AND @fileFormat = 'WAV') OR
(PATINDEX('%for the MP3 version of this track%',@ProductDescription) > 0 AND @fileFormat = 'MP3')
This I believe assumes that all IDs in the description are WAV or all are MP3. That's not the case. Every desription incoming has both WAV and MP3 product IDs.
The distinguisher between which is or is not a WAV or MP3 in a given description are these occurences right after each ProductID in the string:
‘MP3</a>’
‘WAV</a>’
‘for the WAV version of this track’
‘for the MP3 version of this track’
so in other words, for each incoming description, we must check after each ProductID for ‘WAV</a>’ or ‘for the WAV version of this track’ if @Fileformat = 'WAV'
or check for ‘MP3</a>’ or ‘for the MP3 version of this track’ if @FileFormat = 'MP3'
If you look at this one, we could have a mix of MP3 and WAV ProductIDs because any IDs that do not have ‘for the WAV version of this track’ right after the ProductID= means it's an MP3 productID
So
‘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.’
in this one, 106758 & 106763 are MP3 ProductIDs and 105747 is a WAV ProductID
and in this one
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>’
These are MP3 ProductIDs: 105234, 105236, 105238, 105240
These are WAV ProductIDs: 105235, 105237, 105239, 105241
-
ok, I'll also work on changing it as well to see if I can still figure this out on my own but for sure if you think you can help, by all means. I am still not very comfortable with PatIndex combined with the Substring and CHarindex, it gives me head spins. If I come up with it, will post. thanks!
-
Try this instead... Amazing how a coffee at lunch can stimulate the mind!!! This version uses a table variable to store the MP3 and WAV productIDs with a label MP3, WAV, or UNK(nown). Any UNK productIDs are updated at the end UNK => MP3 if WAV is found and UNK => WAV if MP3 is found....
ALTER FUNCTION dbo.GetProductChildIDs(
@productDescription VARCHAR(2000),
@fileFormat CHAR(3)
 
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Location INT
DECLARE @Result varchar(1000)
DECLARE @subString VARCHAR(40)
DECLARE @tblWavMp3 TABLE(
productID INT,
version CHAR(3)
 
SET @result = ''
SET @Location = PATINDEX('%ProductID=%',@ProductDescription)+10
WHILE @location > 10
BEGIN
SET @substring = SUBSTRING(@productDescription, @Location, 30)
-- If WAV insert as WAV
IF PATINDEX ('%WAV%', @substring) > 0
INSERT INTO @tblWavMp3
SELECT SUBSTRING(@substring, 1, CHARINDEX('"', @substring, 1) - 1), 'WAV'
-- If MP3 insert as MP3
IF PATINDEX ('%MP3%', @substring) > 0
INSERT INTO @tblWavMp3
SELECT SUBSTRING(@substring, 1, CHARINDEX('"', @substring, 1) - 1), 'MP3'
-- If neither insert as UNK(nown)
IF PATINDEX ('%WAV%', @substring) = 0 AND PATINDEX ('%MP3%', @substring) = 0
INSERT INTO @tblWavMp3
SELECT SUBSTRING(@substring, 1, CHARINDEX('"', @substring, 1) - 1), 'UNK'
SET @ProductDescription = RIGHT(@ProductDescription,LEN(@ProductDescription) - @Location)
SET @Location = PATINDEX('%ProductID=%',@ProductDescription)+10
END
-- Update Unknowns to MP3 if the knowns are WAV
UPDATE @tblWavMp3
SET version = 'MP3'
WHERE version = 'UNK'
AND EXISTS (SELECT 1 FROM @tblWavMp3 WHERE version = 'WAV')
-- Update Unknowns to WAV if the knowns are MP3
UPDATE @tblWavMp3
SET version = 'WAV'
WHERE version = 'UNK'
AND EXISTS (SELECT 1 FROM @tblWavMp3 WHERE version = 'MP3')
-- Concatenate correct column as defined by @fileFormat
IF @fileFormat = 'MP3'
SELECT @result = @result + CAST(productID AS VARCHAR) + ',' FROM @tblWavMp3 WHERE version = 'MP3'
ELSE
SELECT @result = @result + CAST(productID AS VARCHAR) + ',' FROM @tblWavMp3 WHERE version = 'WAV'
-- Tidy up
SELECT @result = LEFT(@result, LEN(@result) -1)
-- Job Done!!!
RETURN @result
END
GO
-
Guess you were the straw that broke the proverbial Camel's back this morning... have been fighting with some really stupid requirements written by some really stupid System's Analysts for one client that insist that a cursor be present in the code because "there's no other way to do it".
I agree with your disgust about the "cocky sob" that will come back with stupid questions but even after rereading you post, it sounded like you were the cocky one... sorry I came to that conclusion. I should have known better 'cause that's just not the way you operate. Guess it's just the way you said it.
Why did I even bother replying? 'Cause your statement really sounded stupid, at the time, and I thought someone with your intelligence should know about it. Glad I was wrong.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply