Welcome to the Lounge

CREATE FUNCTION justNumeric 
(
	@INP NVARCHAR(20)
)
RETURNS NVARCHAR(20)
AS
BEGIN
	RETURN substring(@INP, PATINDEX('%[0-9]%', @INP), 1+PATINDEX('%[0-9][^0-9]%', @INP+'x')-PATINDEX('%[0-9]%', @INP))
END

SELECT MIN(dbo.justNumeric(ID)), MAX(dbo.justNumeric(ID)), Fruit
FROM Fruits
GROUP BY Fruit

John Simmons / outlaw programmer wrote:

Well, I assume the question I answered was a classroom or homework assignment, so you can't blame a DBA for what was required.

John Simmons / outlaw programmer wrote:

In the interest of full disclosure, I did rant about the assignment, assignments like it, and the idiot instructors that come up with this crap, in my answer.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

SELECT 
    MIN(N.JustNumeric) AS MinID,
    MAX(N.JustNumeric) AS MaxID,
    F.[Fruit] 
FROM 
    [#fruits] As F
    CROSS APPLY ( SELECT substring(F.[ID], PATINDEX('%[0-9]%', F.[ID]), 1 + PATINDEX('%[0-9][^0-9]%', F.[ID] + 'x') - PATINDEX('%[0-9]%', F.[ID])) ) As N (JustNumeric)
GROUP BY 
    F.[Fruit]
;

Slacker007 wrote:

Are you a wizard?

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