Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Nov 21, 2005

User-Defined string Functions Transact-SQL MS SQL Server

By DatabaseJournal.com Staff



>>Script Language and Platform: Transact-SQL MS SQL Server

Updated:

I would like to offer you 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.
  • GETALLWORDS(): Inserts the words from a string into the table.
  • 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 5000 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

Author: Igor2004


Download Script:
UDFs_Transact-SQL.zip


Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued


Back to Database Journal Home



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date