Wouldn't it be great...
-
Well ... there are scalar functions:
CREATE FUNCTION justNumeric
(
@INP NVARCHAR(20)
)
RETURNS NVARCHAR(20)
AS
BEGIN
RETURN substring(@INP, PATINDEX('%[0-9]%', @INP), 1+PATINDEX('%[0-9][^0-9]%', @INP+'x')-PATINDEX('%[0-9]%', @INP))
ENDSELECT MIN(dbo.justNumeric(ID)), MAX(dbo.justNumeric(ID)), Fruit
FROM Fruits
GROUP BY FruitStill messy code though - a DB redesign is a better approach!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
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, 2013 -
John Simmons / outlaw programmer wrote:
a lot of DBA's "aren't programmers"
It has been my experience that a lot of DBAs are not DBAs. :sigh:
Slacker007 wrote:
John Simmons / outlaw programmer wrote:
a lot of DBA's "aren't programmers"
It has been my experience that a lot of DBAs are not DBAs. :sigh:
And not all programmers are programmers. But on the bright side the marketing people know everything about databases and programs, where would we be without them?
Signature ready for installation. Please Reboot 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, 2013Music please, Wouldn't It Be Nice[^] :-\
-
John Simmons / outlaw programmer wrote:
a lot of DBA's "aren't programmers"
It has been my experience that a lot of DBAs are not DBAs. :sigh:
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
-
...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, 2013Well - there are functions, which can do this, sort of. But, I agree. I work with a real DBA (who can program when necessary) and looking at some of his stored procedures and I would definitely go with MACROs to give my weary eyes and mind a rest.
"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
-
...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, 2013It 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 :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.
-
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 :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.
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 -
Well - there are functions, which can do this, sort of. But, I agree. I work with a real DBA (who can program when necessary) and looking at some of his stored procedures and I would definitely go with MACROs to give my weary eyes and mind a rest.
"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
Functions slow queries down. Not a valid workaround. What I want is a simple way to replace repetitive code that always seems to be found SQL queries.
".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 -
...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, 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
-
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, 2013As with everything, it depends How to Make Scalar UDFs Run Faster (SQL Spackle) - SQLServerCentral[^] It's still a butt ugly solution, but it ain't much slower.
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
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.
Wrong is evil and must be defeated. - Jeff Ello
-
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.
Wrong is evil and must be defeated. - Jeff Ello
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
-
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.
