July 9, 2014 at 9:02 pm
Comments posted to this topic are about the item Is It Time To Clear Out Those Quirky Functions?
Best wishes,
Phil Factor
July 9, 2014 at 10:08 pm
I couldn't agree more, it would be nice to clean up some of these things.
Personally I would leave REPLACE alone and add a PATREPLACE.
I would also like to see CHARINDEX/PATINDEX changed to have an occurrence parameter, eg find the 2nd occurence of char in string starting from character 10.
On ISNUMERIC, Grant Saunders recently posted a nice little workaround for it.
July 10, 2014 at 12:59 am
I'd like also a SPLIT/JOIN function as in VB. So, given a tab/cr limited string the function returns a table and viceversa.
PURGECHAR removes a list of chars from a string.
July 10, 2014 at 3:12 am
Carlo Romagnano (7/10/2014)
I'd like also a SPLIT/JOIN function as in VB. So, given a tab/cr limited string the function returns a table and viceversa.PURGECHAR removes a list of chars from a string.
There's plenty of ways to do the split (or indeed) thing for example a nice CTE way:
http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql/
Looking at your experience I'm guessing you did know that 😉 but think a built in way would be preferable. The only thing I might say is that the nuances of what you might want to do often vary for such tasks so maybe rolling your own is preferable? To me there may be better candidates for a renewal, I like the cut of Phil's jib on this one.
I really hate the search-y functions in SQL though - I know they are powerful and indeed flexible, but somehow still absolute pony IMHO.
July 10, 2014 at 5:17 am
I'd love to see a native Regular Expression support. I sometimes use a CLR for pattern matching, but it's a slow alternative to a natively incorporated function. Using Regular Expressions in pattern matching (and for building new strings from old) would be very powerful.
July 10, 2014 at 5:23 am
Top of my list is GROUP_CONCAT, closely followed by REPLACE with wildcards.
July 10, 2014 at 6:30 am
Agree with Phil on ISNUMERIC especially -- unbelievable the language doesn't have a working function for this!
July 10, 2014 at 6:33 am
j.zinn (7/10/2014)
I'd love to see a native Regular Expression support. I sometimes use a CLR for pattern matching, but it's a slow alternative to a natively incorporated function. Using Regular Expressions in pattern matching (and for building new strings from old) would be very powerful.
I agree this would be nice.
July 10, 2014 at 6:58 am
ISNUMERIC('-') returns a 1. Discovered that this week (at an inconvenient time no less).
-- Itzik Ben-Gan 2001
July 10, 2014 at 7:00 am
I would like to see the TRIM available. And why not change ISNULL to work the same way COALESCE does. ISNULL just seems to be more descriptive of what it does, IMHO.
COALESCE by my Webster dictionary means "To grow or come together into one". You are not growing or merging data, you are returning the first not NULL.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
July 10, 2014 at 7:04 am
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]
Best wishes,
Phil Factor
July 10, 2014 at 7:13 am
Yep. The group_concat has to be the most obvious oversight since Bill Gates said 640K is all the RAM anyone will ever need. A Median() over(partition_by) and linear regression trendline functions would be handy. While i'm at it, sign me up for Age_in_years(date_A,date_B) and Age_in_months(date_A,date_B) functions. Is there a more succinct way to say abs(checksum(newid()))%10?
I know, you know, we all know that there are a million ways of doing these in TSQL already. That's kinda the point. You go to a new workplace and discover that they've rolled their own for each of these and not got some of them quite right. This takes that uncertainty and duplication of effort out.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
July 10, 2014 at 7:14 am
I agree on the ISNUMERIC. They should add functions ISMONEY, ISDECIMAL, ISINT, ISBIGINT, etc. Also ISSMALLDATETIME, ISDATETYPE, ISDATETIME2, etc.
July 10, 2014 at 7:19 am
As exciting as it may seem to "clean up" the functions, the suggestion is actually quite bad.
If you want new behavior, the new behavior should be behind a flag, which is off for upgraded installs, and on for new installs, or new method names.
Your mention of DATALENGTH to check the length of a char field. If you got "caught" by that, you didn't actually test your code. It does exactly what it is supposed to do, and documented to do. Also, dead functions like ISNUMERIC? Dead by who's standard? While it does not trap every instance, I would say, the only thing you do for functions like that one is make it more comprehensive so it is more likely to return the right answer, not do away with it.
Just my two cents
July 10, 2014 at 7:26 am
Carlo Romagnano (7/10/2014)
I'd like also a SPLIT/JOIN function as in VB. So, given a tab/cr limited string the function returns a table and viceversa....
I completely agree with this. A splitter question comes up on StackOverflow once a day.
Be still, and know that I am God - Psalm 46:10
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply