|
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
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.
|
|
|
|
|
Something like this, perhaps?
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]
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
modified 23-Mar-18 15:17pm.
|
|
|
|
|
That's just brilliant, if the plan looks like I hope it does it will open up quite a few possibilities for doing things I've sorely missed in SQL-Server.
|
|
|
|
|
The execution plan is identical to John's query.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm going to nest the query a few steps further, that's when it gets interesting.
One of the things I've been sorely missing from Oracle is the 'Materialize' hint in CTEs.
|
|
|
|
|
|
Are you a wizard?
|
|
|
|
|
|
Then you must be the Wizard of lOZt tips
|
|
|
|
|
Slacker007 wrote: Are you a wizard?
No, but he did stay at a holiday inn express last night.
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
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]
modified 23-Mar-18 23:56pm.
|
|
|
|
|
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)
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
PIEBALDconsult wrote: Ideally, the pre-processor also supports including a file from the command line
That is interesting. I never even investigated that possibility.
|
|
|
|
|
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
|
|
|
|
|
Did Caesar's rock collection include Gaul stones?
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Marble-ous! A very intestine thought. Don't let anyone Geode you into changing it as, for bladder or worse, we need these.
Ravings en masse^ |
---|
"The difference between genius and stupidity is that genius has its limits." - Albert Einstein | "If you are searching for perfection in others, then you seek disappointment. If you are seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010 |
|
|
|
|
|
Did he get rid of his old Gaul Stones by a...
Caesarian?
... such stuff as dreams are made on
|
|
|
|
|
You never quite passed that anatomy exam, did you?
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
No but how dya know he did?
... such stuff as dreams are made on
|
|
|
|
|
Is that why Obelix didn't trust him?
Signature ready for installation. Please Reboot now.
|
|
|
|
|
Yes, and he kept them in Saxon his mantle. Occasionally he Picts through them.
If you think 'goto' is evil, try writing an Assembly program without JMP.
|
|
|
|
|
This article[^] was in the Daily Mail discussing online piracy. My thoughts on this is that the content providers are their own worst enemy. I would happily subscribe to 2 or 3 services that delivered the content I want in the format I want (no commercials). Instead the providers insist on creating their own delivery systems. So now I have to subscribe to 3, 4, 5, or more at $10 a month to get the programs I want to see and my list of must watch is not that long. I currently have cable, hulu, and netflix. Now a new Star Trek series comes out and they want me to subscribe to see that one program? No thanks, now I'll see if I can find it somewhere else.
|
|
|
|
|
Nothing wrong with a bit of piracy, harrrr
|
|
|
|