Wouldn't it be great...
-
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.
Wrong is evil and must be defeated. - Jeff Ello
-
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
-
Are you a wizard? :)
I wish I could take credit, but the tip's been doing the rounds for many years now. :) Eg: Complex calculations using cross apply - by Kendra Little[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I wish I could take credit, but the tip's been doing the rounds for many years now. :) Eg: Complex calculations using cross apply - by Kendra Little[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Then you must be the Wizard of lOZt tips :-\
-
Are you a wizard? :)
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 -
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
Checked[^], it should be doable.
Wrong is evil and must be defeated. - Jeff Ello
-
You could create .Net assembly for that C# code and call it in the SQL. My brain works. Hah!
The best way to make your dreams come true is to wake up. Paul Valery
Too dangerous. This exe compiles together with the server and if you mess-up you could kill the server. It happened to me. UDF's are absolutely valid approach in Oracle though. There the libraries written on Java or C++ live in a separate processes. The performance is good too.
There is only one Vera Farmiga and Salma Hayek is her prophet! Advertise here – minimum three posts per day are guaranteed.
-
Functions significantly slow down queries. Not really a valid workaround, IMHO.
".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, 2013Scalar 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.
-
It did reminds me who they are. Prostitutes. Jeremy comment[^]. :)
The best way to make your dreams come true is to wake up. Paul Valery
-
It did reminds me who they are. Prostitutes. Jeremy comment[^]. :)
The best way to make your dreams come true is to wake up. Paul Valery
They do love strip club too. :)
-
...if SQL had macro functionality (kinda like C++) that would help reduce the amount of repetitive bullcrap we have to deal with? For instance, this is part of an answer I posted in the C# forum.
SELECT MIN(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MinID,
MAX(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [Fruit]It would really nice if I could create a macro with the contents of the repeated code:
substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))
Something like this:
declare @justNumeric MACRO =
Which would reduce the first query to this:
SELECT MIN(@justNumeric) AS MinID,
MAX(@justNumeric) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [FruitThis couldn't possibly have an adverse effect on SQL's error reporting incapabilities (
Syntax error near ','), so I'm not concerned with that. Am I just an optimistic dreamer?".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, 2013Something 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] -
...if SQL had macro functionality (kinda like C++) that would help reduce the amount of repetitive bullcrap we have to deal with? For instance, this is part of an answer I posted in the C# forum.
SELECT MIN(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MinID,
MAX(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [Fruit]It would really nice if I could create a macro with the contents of the repeated code:
substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))
Something like this:
declare @justNumeric MACRO =
Which would reduce the first query to this:
SELECT MIN(@justNumeric) AS MinID,
MAX(@justNumeric) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [FruitThis couldn't possibly have an adverse effect on SQL's error reporting incapabilities (
Syntax error near ','), so I'm not concerned with that. Am I just an optimistic dreamer?".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, 2013P.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)
-
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, 2013John 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.
-
...if SQL had macro functionality (kinda like C++) that would help reduce the amount of repetitive bullcrap we have to deal with? For instance, this is part of an answer I posted in the C# forum.
SELECT MIN(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MinID,
MAX(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [Fruit]It would really nice if I could create a macro with the contents of the repeated code:
substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))
Something like this:
declare @justNumeric MACRO =
Which would reduce the first query to this:
SELECT MIN(@justNumeric) AS MinID,
MAX(@justNumeric) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [FruitThis couldn't possibly have an adverse effect on SQL's error reporting incapabilities (
Syntax error near ','), so I'm not concerned with that. Am I just an optimistic dreamer?".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, 2013John 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.
-
...if SQL had macro functionality (kinda like C++) that would help reduce the amount of repetitive bullcrap we have to deal with? For instance, this is part of an answer I posted in the C# forum.
SELECT MIN(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MinID,
MAX(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [Fruit]It would really nice if I could create a macro with the contents of the repeated code:
substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))
Something like this:
declare @justNumeric MACRO =
Which would reduce the first query to this:
SELECT MIN(@justNumeric) AS MinID,
MAX(@justNumeric) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [FruitThis couldn't possibly have an adverse effect on SQL's error reporting incapabilities (
Syntax error near ','), so I'm not concerned with that. Am I just an optimistic dreamer?".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, 2013I 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
-
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.
-
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.
-
Too dangerous. This exe compiles together with the server and if you mess-up you could kill the server. It happened to me. UDF's are absolutely valid approach in Oracle though. There the libraries written on Java or C++ live in a separate processes. The performance is good too.
There is only one Vera Farmiga and Salma Hayek is her prophet! Advertise here – minimum three posts per day are guaranteed.
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
