AB2: Repeating Groups
Now, let's address the same operations that you issued against table AB1, this time with the other 1ANF table, AB2. Take another look at the structure of AB2 in Figure 3. Whereas AB1 has separate columns for each option, AB2 has a single column containing a character string. So how do you deal with a delimited string in T-SQL when the first normal form states that each column must have one value?
The whole structure of AB2 is antirelational, and I urge you again to split this table into two entities if you can. I would suggest that you shouldn't even attempt string functions in T-SQL. SQL Server and the vast majority of relational database engines are optimized for set-based operations, not string operations. You'll probably get much better performance if your application, not your database engine, handles these particular string functions. But for those who are stuck with a table in this format, let's look at how you can use T-SQL statements to manipulate AB2.
First, you need some system functions: the LEN() function to determine the length of a string, SUBSTRING() to return only a portion of a string, and REPLACE() to replace part of a string with a new one. I also recommend using a function to trim the white space from the strings. In this case, you can use LTRIM(RTRIM(string)) to remove the leading and trailing blanks from string.
Let's run through the basic DML operations on this table. First, inserting a new option is easier than you might think. Let's again insert the option Extra Large. The statement
UPDATE AB2
SET Options = Rtrim(Ltrim(Options)) +
",Extra Large" WHERE ItemId = 100
updates the options column of the item with an ItemId of 100 by concatenating the text, 'Extra Large' to the end of the existing string. The statement also trims the text to remove any trailing or leading blanks from the new column value. When you query AB2 now, ItemID 100 returns 'Small,Medium,Large,Extra Large' in the options column.
Deleting an option is a bit more involved. You might think a simple REPLACE() would work, but consider the scenario of deleting 'Large' from the option string. If the string also contained other options that use the word Large (such as Extra Large and Extra Extra Large), all occurrences of the characters 'Large' in those options would be replaced as well. The stored procedure DeleteOption, which Listing 6 shows, addresses all placement and duplication problems within the string.
This procedure takes two parameters: @option, which is the name of the option to be deleted, and @itemid, the item to remove the option from. DeleteOption has four conditional blocks that check for the placement of the option you specify in @option. In this example, let's use 'Large' again:
EXEC DeleteOption 100, "Large"
The procedure's first IF checks to see whether 'Large' is anywhere in the middle of the string by surrounding the string 'Large' with commas and using LIKE to test the condition. If it exists in mid-string, 'Large' and its surrounding commas are replaced with a single comma. Surrounding @option with commas ensures that you replace only 'Large' and not the other options containing those letters.
By adding a comma to the end of @option, the second IF block determines whether 'Large' is in the first position in the option string. The code also ensures that no other characters precede it in the string; without this check, the update might simply remove the letters 'Large' from the first option. The procedure then performs an update, using SUBSTRING() to remove the length of @option plus one character to account for the comma. The third IF block is similar to the second, but it determines whether 'Large' is the last option.
Prev. page
1
2
[3]
4
next page 
