Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    57

    Unanswered: RTRIM or TRIM Function

    I would like to automatically write a users e-mail address based off of the first letter of the user's first name combined with the user's last name. Ex. Bob Smith = bsmith, Sally Jones = sjones etc...

    I don't know which function to use RTRIM or TRIM to take just the first letter of the user's first name.

    Does anyone know how to go about this?

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You need to use trim() to remove leading and trailing spaces and then use substr() to extract the first letter and the surname. To identify where the surname starts use instr() and search for the space char.

    Alan

  3. #3
    Join Date
    Nov 2004
    Posts
    57
    Thanks Alan,

    I think I'll use TRIM combined with TRAILING but my problem is how can I specify how many characters to trim while first names heve different lengths Bob is shorter than Sally?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Use INSTR function and search for first "blank" in the string. Something like this:
    SELECT
    SUBSTR(name, 1, 1) ||
    SUBSTR(name, INSTR(name, ' ', 1, 1) + 1)
    FROM table
    WHERE ...

    It'll return (for name = Bob Smith) BSmith.

    P.S. Hm, right ... but, Alan already told you to do so ... sorry, wasn't paying enough attention to your answer, Alan.
    Last edited by Littlefoot; 02-14-05 at 05:43.

  5. #5
    Join Date
    Nov 2004
    Posts
    57

    Smile

    Thanks Littlefoot,

    You da man!!

  6. #6
    Join Date
    Nov 2004
    Posts
    57
    Littlefoot,

    You forgot 1 little detail to add the last_name record to the expression
    ]


    Code:
    select substr(first_name,1,1||
           substr(first_name,instr(last_name,'',1,1)+1))||
    	   last_name,
    from...

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, I didn't - if you read my post again, you'll see "for name = Bob Smith".
    I agree, my example isn't normalized, but it was for illustration purposes only. I hope you'll forgive me that

Posting Permissions

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