none
Search Multiple Columns with a wildcard.

    Question

  • Hi, hope I am asking this question in the right area...

    I Currently have this line of code:

    'Gen_Anaest=CaseWhen ('9251429')  IN([PROC1],[PROC2],[PROC3],[PROC4])then'Yes'else'No'end

    It works fine although I need to add a wildcard to lookup value, for example:

    'Gen_Anaest=CaseWhen ('9251%')  IN([PROC1],[PROC2],[PROC3],[PROC4])then'Yes'else'No'end

    of course it does not work.

    I know I can do a like statement e.g. [Proc1] like('9251%') or [PROC2] LIKE ('9251%')..... etc

    Although, I have 30 PROC columns and 50 DX Columns that I need to interigated for various codes and the SQL becomes very long.

    Any Suggestions.

    Monday, November 18, 2013 3:35 AM

Answers

All replies

  • Hi

    Try to move this to TSQL Group, I am bit not clear on your full requirement but assuming you are searching for sp having text so try this

    -- Step 1 :
    SELECT ROUTINE_NAME, ROUTINE_DEFINITION
        FROM INFORMATION_SCHEMA.ROUTINES 
        WHERE ROUTINE_DEFINITION LIKE '%9251429%' 
        AND ROUTINE_TYPE='PROCEDURE'
    
    -- Step 2 :
    SELECT OBJECT_NAME(id) 
        FROM SYSCOMMENTS 
        WHERE [text] LIKE '%9251429%' 
        AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
        GROUP BY OBJECT_NAME(id)


    Prav

    Monday, November 18, 2013 5:02 AM
  • Thread Moved

    Brian Coates

    • Marked as answer by Brian64 Monday, November 18, 2013 5:21 AM
    Monday, November 18, 2013 5:20 AM