Wouldn't it be great...

[Single Step Debugger]

Scalar functions are performance killer if you have SQL (selects...) inside. In his case the optimizer commits suicide. But with pure mathematical operations their performance is okay.

There is only one Vera Farmiga and Salma Hayek is her prophet! Advertise here – minimum three posts per day are guaranteed.

[Jagger B]

True, including 'Mongodb'ers. I find out from Jeremy. -JB

[Sandeep Biswha]

They do love strip club too. :)

[PIEBALDconsult]

Something like this (slightly tested) should show that he didn't do it himself:

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]

I also have a table-valued CLR function that uses Regular Expressions, which would suit the assignment. That would be something like:

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]

[PIEBALDconsult]

P.S. ...

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)

[Lost User]

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.

Why keep people saying I can't? I can, and I would. It is the only correct answer, and anything else is unethical.

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.

Good; because someone will find it over Google and try to copy-paste it into his/her solution. Because, "hey, that's how we did it in school" :~

Bastard Programmer from Hell :suss: 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.

[jschell]

John Simmons / outlaw programmer wrote:

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

Every C++ and C compiler that I have ever seen supported an option that allowed one to run only the macro compiler. So one has source code X (text file), with macros, runs it through the compiler and the output is Y(text file) with the macros resolved. I did exactly that at one time with Java files. There is also at least one language that is specifically intended, far as I could tell, to support that sort of functionality. Believe it was called "M". But C++ macros might be better now.

[Mycroft Holmes]

I use Red-Gate SQL Prompt, supplies snippets, not going to do the replace function but you may find it useful - caveat a commercial product.

Never underestimate the power of human stupidity RAH

[PIEBALDconsult]

Right, I do that with C#. Ideally, the pre-processor also supports including a file from the command line so you don't need to put includes in the file.

[jschell]

PIEBALDconsult wrote:

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

That is interesting. I never even investigated that possibility.

[Stephen Gonzalez]

Dude, who said make exe and run in server. I said make .Net assembly(formerly known as DLL). There is big difference between them.

The best way to make your dreams come true is to wake up. Paul Valery