Query throws error when adding a WHERE condition

Transact-SQL
1

I have this query to detect substring patterns within a string:
Example: "Certificate temperature range (20ºC,60ºC)" - I need the query to return (20ºC,60ºC)
Rules

  • get all records that include 'range' in the Text column

  • starting from the Index Position of 'range' get the index of the first opening bracket '(' and the index of the closing bracket ')' to determine the range - String

    SELECT * from (select ITEMID, SUBSTRING(Text, CHARINDEX ('(', Text, CHARINDEX ('range', Text)), CHARINDEX (')', Text, CHARINDEX ('range', Text))-CHARINDEX ('(', Text, CHARINDEX ('range', Text))+1) as Range
    from WK_ITEMTEXT
    where Text like '%range%')a

The query is perfectly executed and all the ranges displayed the way I want; for those records with no 'range' occurrence the range is displayed empty. BUT when I add the where clause where Range <> '' to just get the relevant records, the query throws the error: Invalid length parameter passed to the LEFT or SUBSTRING function.

This does not make any sense to me!

Martin

2

I suspect some of your data is missing a )

-- *** Test Data ***
CREATE TABLE #t
(
    ItemID int NOT NULL
    ,[Text] varchar(255) NOT NULL
);
INSERT INTO #t
VALUES (1, 'Certificate temperature range (20ºC,60ºC)')
    ,(2, 'Certificate (Junk) temperature range (20ºC,60ºC)')
    ,(3, 'Certificate temperature range (20ºC,60ºC');
-- *** End Test Data ***

SELECT T.ItemID
    ,SUBSTRING(T.[Text], B.BStart, CASE B.BEnd WHEN 0 THEN 255 ELSE B.BEnd - B.BStart + 1 END) AS [Range]
FROM #t T
    CROSS APPLY (VALUES(CHARINDEX('range', [Text]))) R(RVal)
    CROSS APPLY (VALUES(CHARINDEX('(', [Text], R.RVal), CHARINDEX(')', [Text], R.RVal))) B(BStart, BEnd)
WHERE R.RVal > 0;
3

Thank you Ifor for your suggestion. I applied your code to my set and stepped on the same error. Basically the error occurs whenever I refer to that query as an alias from outside; I do this to avoid using the long expression for [Range] in the where clause.
SELECT * from (
SELECT T.ItemID, Substring(.........) AS Range
FROM #t T
CROSS APPLY.....
WHERE R.RVal > 0)a
WHERE CHARINDEX ('-', LEFT(Range,4),0) <> 0

THIS throws the Invalid-length-parameter-passed-to-the-LEFT-or-SUBSTRING-function error. While working without the 'Range' alias and placing the whole lengthy expression for it into the WHERE clause behind WHERE R.RVal > 0 works fine.

I did that same thing to my original query and it solved the problem as well!?????

... and I found out that I can still execute the query that uses the alias for 'range' if I limit the result of the inner query by using TOP 2000 (eg. SELECT * from (SELECT TOP 2000....)). The strange thing about it is that this is no real limitation of the record stack as the inner query just returns 1800 records. I can't figure out what causes this behavior, but at least I know now how to get the results. Thank you!

PS. What I do in the where clause is looking for the negative starting value; though the text had been entered manually I check the occurrence of the 'minus' sign within the 4 first characters

4

I've pushed the byte computations down to CROSS APPLYs in order to:
(1) allow alias names to make the code more understandable;
(2) allow data exceptions/missing data to be handled in the CROSS APPLY;
(3) to make the main SELECT less cluttered.

truncate table #t
INSERT INTO #t
VALUES (1, 'Certificate temperature range (20ºC,60ºC)')
      ,(2, 'Certificate (Junk) temperature range (20ºC,60ºC)')
      ,(3, 'Certificate temperature range (20ºC,60ºC)')
      ,(4, 'Word "range" found but ( no closing paren')
      ,(5, 'Range with closing paren ) but no open paren')
      ,(6, 'Ranhe misspelled (20,60).')
      ,(7, 'Range(20,60) no extra spaces at all between values')


SELECT ItemID, Text, SUBSTRING(Text, lparen_start_byte, rparen_start_byte - lparen_start_byte + 1) AS temp_string    
from #t
cross apply (
    select CHARINDEX('range', Text) as range_start_byte
) as assign_alias_names1
cross apply (
    select CHARINDEX('(', Text, range_start_byte + 1) as lparen_start_byte,
        CHARINDEX(')', Text, range_start_byte + 1) as rparen_start_byte
) as assign_alias_names2
where Text like '%range%' and lparen_start_byte > 0 and (rparen_start_byte > lparen_start_byte)
1 Like
5

I've become a fan of using cross/outer apply in order to simplify calculations and make them more understandable. This is an excellent example of that idea.