Post reply

string manipulation - CHARINDEX

  • July 6, 2006 at 2:32 pm

    #114162

    Hi,

    I have a query which retrieves Full_Name and makes it into a format of  ==> lastname+'.'+first letter of first name. (for example: Full_Name: Mary Washington  ==> washington.m)

    Now I want to change to a format of first six letters of lastname+'.'+first letter of first name. (for example: Full_Name: Mary Washington  ==> washin.m)

     Can someone help me to change the query?

    Thank you.

    ------------------------------------

    SELECT  RTRIM(SUBSTRING(Full_Name, CHARINDEX(' ', Full_Name) + 1, CHARINDEX(' ', Full_Name, CHARINDEX(' ', Full_Name) + 1) - CHARINDEX(' ',

                          Full_Name) + 20)) + '.' + LOWER(SUBSTRING(Full_Name, 1, 1)) AS Expr1

    FROM    micros.Mgmt_Personnel_Table

  • July 6, 2006 at 2:54 pm

    #647841

    DECLARE @Mgmt_Personnel_Table TABLE( Full_Name varchar(100))

     INSERT INTO @Mgmt_Personnel_Table VALUES( 'Mary Washington')

    SELECT LOWER( RTRIM( SUBSTRING( Full_Name, CHARINDEX( ' ', Full_Name) + 1, 6))) + '.' + LOWER( SUBSTRING( Full_Name, 1, 1)) AS Expr1

    FROM @Mgmt_Personnel_Table

    I wasn't born stupid - I had to study.

  • July 6, 2006 at 3:40 pm

    #647853

    Thank you for your quick response.  It works great except there's one employee's name is "John Del Valle Kalble", the last name is Del Valle Kalble, first name is John.

    The result for this employee is "del va.m", which should be "delval.m"

    I tried to change it to

    REPLACE(LOWER(RTRIM(SUBSTRING(Full_Name, CHARINDEX(' ', Full_Name) + 1, 6))) + '.' + LOWER(SUBSTRING(Full_Name, 1, 1)), ' ', '') AS Expr1

    But this makes the lastname 5 letters only (delva.m). How to eliminate space in last name and keep it as 6 letters? 

     

  • July 6, 2006 at 4:04 pm

    #647856

    Not sure where the "m" is coming from, but this will get you "delval.j" as well as "washin.m"

    DECLARE @Mgmt_Personnel_Table TABLE( Full_Name varchar(100))

    INSERT INTO @Mgmt_Personnel_Table VALUES( 'Mary Washington')

    INSERT INTO @Mgmt_Personnel_Table VALUES( 'John Del Valle Kalble')

    SELECT LOWER( SUBSTRING( REPLACE( SUBSTRING( Full_Name, CHARINDEX( CHAR(32), Full_Name) + 1, 10), CHAR(32) , ''), 1, 6)) +

                 '.' + LOWER( SUBSTRING( Full_Name, 1, 1)) AS Expr1

    FROM @Mgmt_Personnel_Table

    I wasn't born stupid - I had to study.

  • July 7, 2006 at 7:32 am

    #647968

    Thank you thank you thank you! 

Viewing 5 posts - 1 through 4 (of 4 total)

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