Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Cool Limit amount of characters returned in query - SQL

    Hi folks...

    Can anyone tell me how to limit the amount of characters returned in a query, I need to bring dispaly the first 20 characters only from a 255 varchar field ?

    Thanks in advance if you can.

    Donald

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,407
    Depending on what SQL engine you are using the LEFT function may do what you want

    eg select left(<mycolumn>,20) as <myColumnsFirst20Characters> from <mytable>
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2003
    Posts
    34

    use convert

    Quote Originally Posted by donaldt
    Hi folks...

    Can anyone tell me how to limit the amount of characters returned in a query, I need to bring dispaly the first 20 characters only from a 255 varchar field ?

    Thanks in advance if you can.

    Donald
    select convert(varchar(20), <field>) from <table name> should give you first 20 characters from a field.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    Or, in Oracle, use the SUBSTR function:

    SELECT SUBSTR(column, start_position, desired_length) FROM some_table;
    which would then be
    SELECT SUBSTR(column, 1, 20) FROM some_table;

  5. #5
    Join Date
    Dec 2003
    Posts
    39
    If u want to show max 20 characters of all query results, u can change the options at Query Analyzer :
    Tools - Options - Results - Maximum characters per column
    Link Link

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    The "standard SQL" way to do this is
    Code:
    CAST(my_column AS CHAR(20))
    This should also be the most efficient way of doing things since CAST is not a scalar function but a built-in functionality.
    Of course, most people will use a scalar function like SUBSTR, SUBSTRING or LEFT, but for one thing these names are not standardised so your SQL becomes less portable, and secondly there may be a performance hit when calling scalar functions.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •