Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: How To Find Fields Containing One String Character?

    Hey guys,

    I am creating a query that will return all member names where their first name contains a single character.

    I can't find any db2 functions that will work for me. I can accomplish this in SQL with "where DATA_LENGTH(FNAME) = 1", but I can't find a DB2 equivalent to this function.

    The string functions don't seem to work because they would be looking for specific values. Can anyone help?

    Thanks!

  2. #2
    Join Date
    Jan 2009
    Posts
    46
    I just found the solution:

    where LENGTH(RTRIM(NAME)) = 1

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    At least up to the SQL standard 2003, DATA_LENGTH is not a standardized function. So for more portable code, you shouldn't rely on it in other database systems either.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    where length(replace(FNAME,' ','') = 1
    or
    where length(strip(FNAME,B,' ') = 1

    will preclude your overlooking FNAMEs that start with a space and then a character.
    Last edited by dbzTHEdinosaur; 07-19-12 at 16:39. Reason: added strip scalar function
    Dick Brenholtz, Ami in Deutschland

Posting Permissions

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