July 10, 2014 at 7:51 am
j.zinn (7/10/2014)
...Regular Expression support...
I vote for this too! And yes trim would be very useful.
Hakim Ali
www.sqlzen.com
July 10, 2014 at 8:04 am
Tops on my SQL wish list would be a GROUP BY function that would concatenate string fields - like a SUM() function sums up numeric values in the group, this new function would string together character values in the group using a specified delimiter character. I've needed something like this for years, but have had to created a stored procedure loop to do this. This may be what GPO noted previously. Please make this happen.
July 10, 2014 at 9:08 am
What else would you add by way of built-in string operators and functions?
A 'prettify' function would be nice, which would capitalise the first letter of each word in the string argument. For example:
PRETTIFY('phil factor')
would return the result
Phil Factor.
July 10, 2014 at 9:54 am
jgoodwin 14842 (7/10/2014)
As exciting as it may seem to "clean up" the functions, the suggestion is actually quite bad...
I agree but that it could be bad and could cause an unexpected result and cost. If "clean up" means we stop using them in any new work we do, that is fine. We clean up our act and code right from now on. However, this topic also brings up the question of should we start to readdress the backlog of code we have in our shop to eliminate the old clunky way of doing things? This would be sort of a clandestine effort to make it better SQL and conform to the technical standard newly put in place by us. If you answer yes to that you might want at minimum to speak with your boss before you start addressing code that is working in production.
Now if you want to define a project, where you address conversions of old functions in production code with anticipated risks stated and understood, and your management buying into it before you get underway, you might be far better off.
Also if you adopt a strategy that when you are addressing a problem or enhancing certain functions that at that time you address the silly old functions problem and make it more standard, that also would be a good idea.
Just don't go off into an adventure on your own to make it work the way you want without approval of management, and them understanding what you are doing, the value, and the risks.
M...
Not all gray hairs are Dinosaurs!
July 10, 2014 at 10:30 am
IsNumeric() can be very useful if fixed to work correctly.
July 10, 2014 at 10:33 am
To be honest, MS spends a shedload of time deprecating and removing things, some of which are incredibly useful. For example, just because many folks don't understand what ISNUMERIC is actually supposed to do doesn't mean that it needs to be changed or removed. (Note that ISNUMERIC was NEVER intended to be an ISALLDIGITS function!).
I'd like to see MS spend less time deprecating and spend more time on adding the functions that we'd all like to see. I'd also like to see them fix "new" functions, like PIVOT (for example), so that they operate at least as well as that found in ACCESS.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2014 at 10:43 am
Phil Factor (7/10/2014)
Here is a LTRIM function that works for whitespace[font="Courier New"]
-- does a particular procedure exist
IF EXISTS ( SELECT 1
FROM information_schema.Routines
WHERE ROUTINE_NAME = 'LeftTrim'--name of procedire
AND ROUTINE_TYPE = 'FUNCTION'--for a function --'FUNCTION'
AND ROUTINE_SCHEMA = 'DBO' )
SET NOEXEC ON
GO
-- if the routine exists this stub creation stem is parsed but not executed
CREATE FUNCTION LeftTrim (@String VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN 'created, but not implemented yet.'--just anything will do
END
GO
SET NOEXEC OFF
-- the following section will be always executed
GO
ALTER FUNCTION dbo.LeftTrim (@String VARCHAR(MAX)) RETURNS VARCHAR(MAX)
/**
summary: >
This function returns a string with all leading white space removed. It is similar to the LTRIM functions in most current computer languages.
Author: Phil Factor
date: 28 Jun 2014
example:
- code: select dbo.LeftTrim(CHAR(13)+CHAR(10)+' 678ABC')
- code: Select dbo.LeftTrim(' left-Trim This')
returns: >
Input string without leading white-space
**/
AS
BEGIN
RETURN STUFF(' '+@string,1,PATINDEX('%[^'+CHAR(0)+'- '+CHAR(160)+']%',' '+@string+'!' collate SQL_Latin1_General_CP850_Bin)-1,'')
END
GO
--now do some quichk assertion tests to make sure nothing is broken
IF dbo.LeftTrim('
This is left-trimmed') <> 'This is left-trimmed' RAISERROR ('failed first test',16,1)
IF dbo.LeftTrim('') <> '' RAISERROR ('failed Second test',16,1)
IF dbo.LeftTrim(' ') <> '' RAISERROR ('failed Third test',16,1)
IF NOT dbo.LeftTrim(NULL) IS NULL RAISERROR ('failed Fourth test',16,1)
IF dbo.LeftTrim(CHAR(0)+' '+CHAR(160)+'Does this work?')<>'Does this work?' RAISERROR ('failed fifth test',16,1)[/font]
Phil, you can make this more than twice as fast by turning it into an inline table value function like this:
CREATE FUNCTION dbo.itvf_LeftTrim (@String VARCHAR(MAX))
RETURNS TABLE AS
RETURN
SELECT string = STUFF(' '+@string,1,PATINDEX('%[^'+CHAR(0)+'- '+CHAR(160)+']%',' '+@string+'!' collate SQL_Latin1_General_CP850_Bin)-1,'')
GO
Test harness:
-- TEST HARNESS
IF OBJECT_ID('tempdb..#test') is not null drop table #test;
SELECT TOP (100000) string = ' xxx'
INTO #test
FROM sys.all_columns a CROSS JOIN sys.all_columns b
-- COMPARE THE 2 solutions
DBCC FREEPROCCACHE with no_infomsgs;
DBCC DROPCLEANBUFFERS with no_infomsgs;
SET NOCOUNT ON;
SET STATISTICS TIME ON;
PRINT 'lefttrim:'+char(10)
SELECT dbo.lefttrim(string)
FROM #test;
PRINT 'itvf_lefttrim:'+char(10)
SELECT x.string
FROM #test
CROSS APPLY dbo.itvf_LeftTrim(string) x;
SET NOCOUNT OFF;
SET STATISTICS TIME OFF;
GO
Here's my test results (cold cache):
lefttrim:
SQL Server Execution Times:
CPU time = 5859 ms, elapsed time = 6648 ms.
itvf_lefttrim:
SQL Server Execution Times:
CPU time = 1797 ms, elapsed time = 2694 ms.
-- Itzik Ben-Gan 2001
July 10, 2014 at 11:23 am
Alan.B (7/10/2014)
Phil, you can make this more than twice as fast by turning it into an inline table value function like this:
To that, I say... are you sure? Please read the following article before you answer that question.
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2014 at 11:44 am
I understand it is designed to cover all the data types, which is sometimes misleading, since values like: 1e2 don't translate into all the datatypes, but I dare say the following is not really desirable, either.
/*
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include the following:
int, numeric, bigint, money, smallint, smallmoney, tinyint, float, decimal, real
*/
SELECT ISNUMERIC('.'),
ISNUMERIC(','),
ISNUMERIC('$')
July 10, 2014 at 12:04 pm
jgoodwin 14842 (7/10/2014)
I understand it is designed to cover all the data types, which is sometimes misleading, since values like: 1e2 don't translate into all the datatypes, but I dare say the following is not really desirable, either./*
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include the following:
int, numeric, bigint, money, smallint, smallmoney, tinyint, float, decimal, real
*/
SELECT ISNUMERIC('.'),
ISNUMERIC(','),
ISNUMERIC('$')
Beauty is in the eyes of the beholder. 😀 I think those are great because they all evaluate to ZERO when cast to money.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2014 at 2:06 pm
so that they operate at least as well as that found in ACCESS
He speaks the truth. Which is both sad and funny. As someone who cut their teeth on Access, I was amazed at what you could do when I moved to SQL Server. But I really missed CROSSTAB.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
July 10, 2014 at 2:23 pm
Could a second parameter be added to ISNUMERIC to determine a specific numeric type?
I'd love a wildcard REPLACE as well.
July 10, 2014 at 2:30 pm
Please, please make the error/warning messages better and more precise...Nothing worse than something like this:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Come on SQL Server, you know where the error is, why not share?
Anton
July 10, 2014 at 2:49 pm
aochss (7/10/2014)
Please, please make the error/warning messages better and more precise...Nothing worse than something like this:Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Come on SQL Server, you know where the error is, why not share?
Anton
This +100! I hear the same from our devs when they get "Object Reference" errors. You know which one is the issue - return it and make it easier to debug. :-/
(Other good suggestions along the way as well, but this one is really annoying as you don't have any real way short of parsing all sorts of data to figure out where the issue starts.)
July 10, 2014 at 3:02 pm
Luis Cazares (7/10/2014)
Could a second parameter be added to ISNUMERIC to determine a specific numeric type?I'd love a wildcard REPLACE as well.
See, now this is a useful change.
John
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply