marc.eilander 13301 (7/31/2013)
Hi, Thank you for your response. However the goal is to only retrieve those lines starting with Int.nr.TXT:. I checked the data, all lines have a valid integer after the :. However I still do not get why, despite the where clause, SQL is trying to fetch and evaluate all rows, also those not starting with the Int.nr.TXT..
I think Stefan could be right. Try this test harness, then try query 2 on your data:
DROP TABLE #frstx
CREATE TABLE #frstx (ProjectID INT, TextDescription text)
INSERT INTO #frstx (ProjectID, TextDescription)
SELECT 26, 'normal text in this field' UNION ALL
SELECT 26, 'special text in this field' UNION ALL
SELECT 26, 'Int.nr.TXT: 69000'
-- original subquery
SELECT SUBSTRING(CAST(X.TextDescription AS VARCHAR(100)),13,10)
FROM #frstx X
WHERE (LEFT(CAST(X.TextDescription AS VARCHAR(100)), 11) = 'Int.nr.TXT:')
-- fixed for deferred expression evaluation
SELECT b.TTT_ID, X.TextDescription
FROM #frstx X
CROSS APPLY (SELECT ChosenRow = CASE WHEN X.TextDescription LIKE 'Int.nr.TXT:%' THEN 1 ELSE 0 END) a
CROSS APPLY (
SELECT TTT_ID = CASE WHEN ChosenRow = 1 THEN CAST(SUBSTRING(CAST(X.TextDescription AS VARCHAR(100)),13,10) AS INT) END) b
WHERE ChosenRow = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden