Post reply

New Syntax ???

  • November 1, 2006 at 12:54 pm

    #121201

    Hello All

    I had a fellow employee present me with the following syntax for a query.

    SELECT

    Col1

    FROM Table1

    WHERE { fn LENGTH(Col1) } > 0

    Apparently this syntax was produced when he used the "New View" Gui in SQL 2005.  I generally never use the GUI, so i would have written the statement like this

    SELECT Col1

    FROM Table1

    WHERE LEN(Col1) > 0

    Can someone explain the fn... syntax, or provide a help file reference?  Is it something I should be exploring?  I have never seen it before and have been unsuccessful in locating a good help file reference.

    Thanks in advance

    Steve

  • November 1, 2006 at 1:39 pm

    #669329

    hadn't seen it before either; tested it and it works on my sqk2k machine:

    SELECT name

    FROM sysobjects

    WHERE { fn LENGTH(name) } > 30

    [edited] sure enough, it's in the BOL:

    Functions for Expressions

    You can call a number of functions when you are building an expression, including:

    • String (character) functions
    • Date functions
    • Mathematical functions
    • System functions
    • Other functions, such as those to convert data from one type to another
    • User-defined functions

    For more information, see Functions.

    If you are creating queries (not views, stored procedures, or triggers) that might be run against different databases, you can also use ODBC functions. ODBC syntax includes the "fn" qualifier in front of the function name and braces around the entire function. For example, the following expression uses an ODBC function to convert text to lowercase letters:

    {fn LCASE ( address ) }

    The Query Designer can help you work with functions by:

    • Correctly inserting quotation marks in function arguments
    • Validating the data types of arguments
    • Validating the data types of return values

    For information on ODBC, see the Data Access Services section of the MSDN® Online Library Microsoft Web site. For more information on functions, see User-Defined Functions.

    Note   You can use a special set of functions, the aggregate functions such as SUM( ) and AVG( ), to create queries that summarize data. For details, see Summarizing and Grouping.

    String Functions

    The following table contains samples of string functions.  For more information, see String Functions and Using String Functions.

    FunctionDescriptionExample
    LCASE( )1,

    LOWER( )

    		Converts strings to lowercase

    SELECT UPPER(substring(lname, 1, 1)) + 
      LOWER(substring (lname, 2, 99))FROM employee

    Displays a last name after the first character is converted to uppercase and the remaining characters to lowercase.

    LTRIM( )Removes leading spaces from a string

    SELECT stor_name, LTRIM(stor_address)FROM stores
    Displays an address column after extraneous spaces are removed from the front.
    SUBSTRING( )Extracts one or more characters from a string
    SELECT SUBSTRING(phone,1,3)FROM employee
    

    Displays the first three characters (the area code) of a phone number.
    UCASE( )1,
    UPPER( )
    		Converts strings to uppercase
    SELECT * FROM employeeWHERE UPPER(lname) = 'SMITH'
    Converts the contents of the lname column to uppercase before comparing them to a specific value (avoids mismatches if the search is case sensitive). For details about case sensitivity in SQL Server, see Query Designer Considerations .
    1    If calling as an ODBC function, use syntax such as: { fn LCASE(text) }.
    Date Functions
    The following table contains samples of date functions.  For more information, see Date and Time Functions.
    FunctionDescriptionExample
    DATEDIFF( )Calculates an interval between two dates.
    SELECT fname, lname, hire_dateFROM employeeWHERE DATEDIFF(year, hire_date, getdate()) > 5
    

    Locates all employees hired more than five years ago.
    DATEPART( )Returns the specified portion of a date or datetime column, including the day, month, or year.
    SELECT DATEPART(year, hire_date)FROM employee
    

    Displays only the year in which an employee was hired (not the full date).
    CURDATE( )1,
    GETDATE( ) or DATE( )
    		Returns the current date in datetime format. This function is useful as input for many other date functions, such as calculating an interval forward or backward from today.
    SELECT order_idFROM ordersWHERE order_date = GETDATE()
    

    Displays orders placed today.
    1    If calling as an ODBC function, use syntax such as: { fn CURDATE() }.
    Mathematical Functions
    The following functions are typical of those available in many databases.  Refer to Mathematical Functions for more information.
    Note   You can use the aggregate functions AVG( ), COUNT( ), MAX( ), MIN( ), and SUM( ) to create averages and totals in your report. For details, see Summarizing and Grouping.
    FunctionDescriptionExample
    ROUND( )Rounds a number off to the specified number of decimal places
    SELECT ROUND(qty * (price * discount), 2)FROM sales
    

    Displays a total price based on a discount, then rounds the results off to two decimal places.
    FLOOR( )Rounds a number down to the nearest (smallest) whole number
    UPDATE titlesSET price = FLOOR(price)
    

    Rounds all prices in the titles table down to the nearest whole number.
    CEILING( )Rounds a number up to the nearest whole number
    INSERT INTO archivetitle
    

    SELECT title, CEILING(price)FROM titles
    Copies the title and the price (rounded up to the nearest integer) from the titles table to the archivetitle table.
    System Functions
    The following functions are typical of those available in many databases.  For more information, see System Functions.
    FunctionDescriptionExample
    DATALENGTH( )Returns the number of bytes used by the specified expression
    SELECT DATALENGTH(au_lname + ', ' 
    

      + au_fname)FROM authors
    Lists the number of bytes required for the combination of last and first names.
    USER( )1,
    USER_NAME( )
    		Returns the current user name
    SELECT company_name, city, phone
    

    FROM customersWHERE salesperson = USER_NAME()
    Creates a list of customers for the salesperson who runs the query.
    1    If calling as an ODBC function, use syntax such as: { fn USER() }.
    Other Functions
    The following functions illustrate utility functions available in many databases.  For more information, see Functions.
    FunctionDescriptionExample
    CONVERT( )Converts data from one data type into another. Useful to format data or to use the contents of a data column as an argument in a function that requires a different data type.
    SELECT 'Hired: ' + CONVERT(char (11),
    

      hire_date)FROM employee
    Displays a date with a caption in front of it; the CONVERT( ) function creates a string out of the date so that it can be concatenated with a literal string.
    SOUNDEX( )Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches.
    SELECT au_lname, au_fnameFROM authorsWHERE SOUNDEX(au_fname) = 'M240'
    

    Searches for names that sound like "Michael".
    STR( )Converts numeric data into a character string so you can manipulate it with text operators.
    SELECT str(job_id) + ' ' +    str(job_lvl)FROM employee
    

    Displays the job_id and job_lvl columns (both numeric) in a single string.
    Lowell
    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
     
      
    •  
Viewing 2 posts - 1 through 1 (of 1 total)
 
You must be logged in to reply to this topic. Login to reply