Home Forums SQL Server 2008 T-SQL (SS2K8) Replace string after a specific index RE: Replace string after a specific index

  • March 23, 2015 at 9:43 am

    #1785743

    sarath.tata (3/23/2015)

    I think I may better use Left in stead of Replace

    SELECT

    CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN

    LEFT(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1))

    ELSE Supervisor END AS NewSupervisor

    FROM data d

    -- This too

    SELECT

    OldSupervisor = Supervisor,

    x.[Type],

    NewSupervisor = CASE

    WHEN [Type] = 0 THEN Supervisor

    ELSE LEFT(Supervisor, LEN(Supervisor)-CHARINDEX(' ',REVERSE(Supervisor))) END

    FROM #data

    CROSS APPLY (SELECT [Type] = CASE WHEN RTRIM(Supervisor) LIKE '%, % %' THEN 1 ELSE 0 END) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden