July 16, 2011 at 1:02 pm
I am studying for the microsoft exam 70-433, Database Development exam.
The following study question is stumping me:
"Your company uses a table named Products to store product information. The table uses a column named productDescription which stores a text string. This string has the following format":
'Short <product name> Description:
-BEGIN-
<short product description>
-END-
Long <product name> Description:
-BEGIN-
<long product description>
-END-'
You must retrieve only the long product description for each row in the table. Write the query that will do this.
The answer is:
SELECT
SUBSTRING(
productDescription,
CHARINDEX('-BEGIN-', productDescription,
(PATINDEX('%Long%Description%', productDescription))) + 7,
CHARINDEX('-END-', productDescription,
(PATINDEX('%Long%Description%', productDescription))) -
(CHARINDEX('-BEGIN-', productDescription,
(PATINDEX('%Long%Description%', productDescription))) + 7))
FROM Products;
I don't understand how the (PATINDEX()) can actually return anything but the starting position of the work 'Long'. I don't see any examples on-line where PATINDEX is passed two strings within wildcards, and if I try it against adventureworks then it returns only the position of the first string, ignoring the second. For example:
USE AdventureWorks2008R2;
GO
SELECT PATINDEX('%ensure%front%',DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
GO
the above returns only the position 64, for the string 'ensure'. What is (PATINDEX('%Long%Description%', productDescription))) accomplishing?
thanks.
July 19, 2011 at 11:55 pm
The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. PATINDEX can use wildcard characters, but CHARINDEX cannot.
From BOL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply