Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Is It Time To Clear Out Those Quirky Functions? Expand / Collapse
Author
Message
Posted Wednesday, July 9, 2014 9:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 587, Visits: 2,544
Comments posted to this topic are about the item Is It Time To Clear Out Those Quirky Functions?


Best wishes,

Phil Factor
Simple Talk
Post #1590988
Posted Wednesday, July 9, 2014 10:08 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 1,054, Visits: 3,122
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.
Post #1590993
Posted Thursday, July 10, 2014 12:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 2,556, Visits: 2,417
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.
Post #1591028
Posted Thursday, July 10, 2014 3:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 1,729, Visits: 1,141
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.
Post #1591060
Posted Thursday, July 10, 2014 5:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 10, 2014 12:42 PM
Points: 230, Visits: 50
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.
Post #1591085
Posted Thursday, July 10, 2014 5:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:10 AM
Points: 442, Visits: 10,949
Top of my list is GROUP_CONCAT, closely followed by REPLACE with wildcards.
Post #1591089
Posted Thursday, July 10, 2014 6:30 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:00 AM
Points: 39, Visits: 301
Agree with Phil on ISNUMERIC especially -- unbelievable the language doesn't have a working function for this!
Post #1591119
Posted Thursday, July 10, 2014 6:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 6:46 AM
Points: 35, Visits: 160
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.
Post #1591120
Posted Thursday, July 10, 2014 6:58 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
ISNUMERIC('-') returns a 1. Discovered that this week (at an inconvenient time no less).

-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1591129
Posted Thursday, July 10, 2014 7:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 289, Visits: 1,099
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.
Post #1591130
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse