Home Forums SQL Server 2008 SQL Server 2008 - General Using CASE Condition for Prefixing a field from a table RE: Using CASE Condition for Prefixing a field from a table

  • September 30, 2013 at 8:14 am

    #1654569

    This will depend on the number of levels you want to go with this, but this should accomplish what you wanted:

    CASE

    when PATINDEX('%Z%',EmployeeCode) > 0 then REPLICATE('A',PATINDEX('%[0-9]%',EmployeeCode)) + SUBSTRING(EMPLOYEECODE,PATINDEX('%[0-9]%',EmployeeCode),99)

    WHEN ISNUMERIC(LEFT(EMPLOYEECODE,1)) = 1 THEN 'A'+EMPLOYEECODE

    ELSE CHAR(ASCII(LEFT(EMPLOYEECODE,1))+1)+SUBSTRING(EMPLOYEECODE,2,99)

    END


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]