SQL Server Performance

User-Defined string Functions T-SQL (New)

Discussion in 'Contribute Your SQL Server Scripts' started by Igor2004, May 28, 2005.

  1. Igor2004 New Member

    Ladies and Gentlemen,

    I am pleased to offer, free of charge, the following string functions Transact-SQL:

    AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
    RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
    OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
    OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps).
    PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
    PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
    PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
    CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
    STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
    STRFILTER(): Removes all characters from a string except those specified.
    GETWORDCOUNT(): Counts the words in a string.
    GETWORDNUM(): Returns a specified word from a string.
    PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
    RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
    ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
    ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).

    AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.

    More than 4000 people have already downloaded my functions. I hope you will find them useful as well.

    For more information about string UDFs Transact-SQL please visit the
    http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115

    Please, download the file
    http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115

    With the best regards,
    Igor Nikiforov.

    --------------------------------------------------------------
    To Igor Nikiforov,

    Thank you for sharing your code. I Googled 'SQL count occurrence of
    character in a string' and bingo! Didn't have to write it and got so
    much more.

    Appreciate it.



    Phil Youker
    Programmer/Analyst
    VCSSO/Information Technology Services

  2. Madhivanan Moderator

    Thanks for the links


    Madhivanan

    Failing to plan is Planning to fail
  3. surendrakalekar New Member

  4. Igor2004 New Member

    I just have written 2 new functions
    CHRTRAN() Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression
    STRFILTER() Removes all characters from a string except those specified
  5. ranjitjain New Member

    Hi igor2004,
    I found strfilter as good handy function.
    Thanx alot
  6. surendrakalekar New Member

    quote:Originally posted by Igor2004

    I just have written 2 new functions
    CHRTRAN() Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression
    STRFILTER() Removes all characters from a string except those specified

    Excellent.
    Thank you for sharing your UDF.


    Surendra Kalekar

  7. ushivamurthy New Member

    Igor- you are a champion, thanks heaps for those precious UDFs.
    Regards,
    Uday
  8. tirupathi_rao New Member

    thanks for these functions

Share This Page