Post reply

Trying to Trim Words From a String

  • December 27, 2010 at 12:23 pm

    #229098

    I have a table that has a column with employee names and who they report to. Example:

    Jane Doe report to John Doe

    Jim Smith report to June Smith

    I only want to pull the first and last names and leave of the "report to . . .". Is there any way to do this?

    Thanks.

  • December 27, 2010 at 12:35 pm

    #1266548

    Something like this:

    DECLARE @String VARCHAR(1000);

    SELECT @String = 'John Smith reports to Jane Smith';

    SELECT LEFT(@String, PATINDEX('%reports to%', @String)-2);

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 27, 2010 at 12:49 pm

    #1266551

    Thanks. This gets me closer, but I have to pull from over 3,000,000 records. I am trying fit fit the column name in your solution somehow, but I am not having much luck. Could you assist further, please?

    btw, the column name is UserDesc and the table name is UserNames.

  • December 27, 2010 at 1:16 pm

    #1266556

    SELECT LEFT(UserDesc, PATINDEX('%reports to%', UserDesc)-2)

    from UserNames;

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 27, 2010 at 2:24 pm

    #1266585

    Thank you very much! That did it.

  • December 27, 2010 at 2:25 pm

    #1266586

    You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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