|
It is nonsense to have a VARCHAR as primary key. SQL92 is all you need, this fancy stuff is just there to hide that the DBA doesn't know what he is doing.
An abomination.
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.
|
|
|
|
|
Well, I assume the question I answered was a classroom or homework assignment, so you can't blame a DBA for what was required. 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.
".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
|
|
|
|
|
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.
|
|
|
|
|