Is It Time To Clear Out Those Quirky Functions?

  • Comments posted to this topic are about the item Is It Time To Clear Out Those Quirky Functions?

    Best wishes,
    Phil Factor

  • 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.

  • 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.

  • 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.

  • 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.

  • Top of my list is GROUP_CONCAT, closely followed by REPLACE with wildcards.

  • Agree with Phil on ISNUMERIC especially -- unbelievable the language doesn't have a working function for this!

  • 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.

  • ISNUMERIC('-') returns a 1. Discovered that this week (at an inconvenient time no less).

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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.

  • 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

  • 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

  • I agree on the ISNUMERIC. They should add functions ISMONEY, ISDECIMAL, ISINT, ISBIGINT, etc. Also ISSMALLDATETIME, ISDATETYPE, ISDATETIME2, etc.

  • 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

  • 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.


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply