|
|
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
|
|
|
|
|
I thought it was the only way you get all those ARRRRR rated movies!
I'm pretty sure I would not like to live in a world in which I would never be offended.
I am absolutely certain I don't want to live in a world in which you would never be offended.
Freedom doesn't mean the absence of things you don't like.
Dave
|
|
|
|
|
is the soapbox broken?
Signature ready for installation. Please Reboot now.
|
|
|
|
|
|
This is exactly why I have given up on watching anyone TV show. If its not on one of the services I subscribe to, then I don't care.
Though, if I could find all of the GunSmoke episodes and a few other TV shows from that era...As they are no longer shown any where I have found.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|