Welcome to the Lounge

WITH [cte0] AS
(
  SELECT [Fruit]
  , [ID]
  , PATINDEX ( '%[0-9]%'       , [ID] ) [start]
  , PATINDEX ( '%[0-9][^0-9]%' , [ID] ) [end]
  FROM [#fruits]
)
, [cte1] AS
(
  SELECT [Fruit]
  , [ID]
  , SUBSTRING ( [ID] , [start] , CASE WHEN [end] = 0 THEN LEN ( [ID] ) ELSE [end] END ) [numeric]
  FROM [cte0] 
)
SELECT MIN ( [numeric] ) AS MinID
, MAX ( [numeric] ) AS MaxID
, [Fruit] 
FROM [cte1] 
GROUP BY [Fruit]

SELECT MIN ( B.[Match] ) AS MinID
, MAX ( B.[Match] ) AS MaxID
, A.[Fruit] 
FROM [#fruits] A
CROSS APPLY Library.dbo.RegEx ( [ID] , '^\d+' ) B
GROUP BY A.[Fruit]

f:\>type jsop.sql

# define NUMERIC(x) (SUBSTRING([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID])))

SELECT MIN ( NUMERIC ( [ID] ) ) [MinID]
, MAX ( NUMERIC ( [ID] ) ) [MaxID]
, [Fruit]
FROM [fruits]
GROUP BY [Fruit]

f:\>"F:\mingw\bin\cpp" -P -C JSOP.sql | sqlcmd -E -S localhost -d junk
MinID      MaxID      Fruit
---------- ---------- ----------------
1          3          Apple
4          5          Orange

(2 rows affected)

John Simmons / outlaw programmer wrote:

if SQL had macro functionality (kinda like C++)

PIEBALDconsult wrote:

Ideally, the pre-processor also supports including a file from the command line