Wouldn't it be great...
-
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