Post reply

converting name..

  • June 26, 2003 at 9:37 am

    #161818

    I need to convert (lastname, firstname, middle initial) to (first letter of firstname, first letter of middle, and lastname)...I tried to use cast and cacantenate but can't get it to work.

    thanks

    existing column: want result:

    Smith, John B jbsmith

    doe, john jdoe

  • June 26, 2003 at 11:21 am

    #463297

    Try:

    SELECT SUBSTRING(<existing column>, CHARINDEX(' ', <existing column>, 1) + 1, 1) +

    CASE WHEN CHARINDEX(' ', <existing column>, CHARINDEX(' ', <existing column>, 1) + 1) > 0 THEN SUBSTRING(<existing column>, CHARINDEX(' ', <existing column>, CHARINDEX(' ', <existing column>, 1) + 1) + 1, 1) ELSE '' END +

    SUBSTRING(<existing column>, 1, CHARINDEX(',', <existing column>, 1) - 1)

    This does make a couple of assumptions. 1. The full name is in the <existing column>. 2. The <existing column> is always formatted like 'Last, First Middle' or 'Last, First'.

  • June 28, 2003 at 8:31 pm

    #463579

    I"ve used the following, but DAVNovak's idea is pretty cool

    SELECT LEFT(Firstname, 1)

    + " " LEFT(Middle, 1)

    + " " Lastname

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply