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)
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
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!