CODEWITHSUNDEEP
sqloracle-database
user3847566
user3847566

Reputation: 53

Issue in Searching Using 'LIKE' in Oracle

I have a concern in searching a string in oracle where i am using string search like 'M_' and it's not working. Would someone give me a helping hand in sorting this:

    SELECT UNIQUE
    (SELECT SUBSTR (E.PERSON_NAME,
    1,
    INSTR (PERSON_NAME, ',', 1) - 1)
    FROM SEC_PERSON E
    WHERE PERSON_AID = LV_USERID)
    first_Name,
    (SELECT SUBSTR (E.PERSON_NAME,
    INSTR (E.PERSON_NAME,
    ',',
    -1,
    1)
    + 1)
    FROM SEC_PERSON E
    WHERE PERSON_AID = LV_USERID)
    last_Name,
    (SELECT FK_ROLE_ID
    FROM SEC_APPLICATION_PERSON_ASSOC
    WHERE FK_PERSON_AID = LV_USERID)
    user_Role,
    A.MARKET_SEGMENT_NAME market_Name,
    A.MARKET_SEGMENT_ID marker_Id,
    B.SECTOR_NAME sector_Name,
    B.SECTOR_NAME_UPPER sector_Display_Name,
    B.SECTOR_ID sector_Id,
    D.EEQ_SOL_NAME end_Equipment_Name,
    D.EEQ_SOL_NAME_UPPER end_Equipment_Display_Name,
    D.EEQ_SOL_ID end_Equipment_Id,
    NVL(D.APPROVED_FLAG,'N') APPROVED_FLAG,
    NVL(D.CCB_FLAG,'N') CCB_FLAG,
    C.WEB_CATEGORY_NAME web_Category_Name,
    C.WEB_CATEGORY_ID web_Category_Id,
    NVL(D.EXPORT_CONTROL_FLAG,'N') export_Flag,
    E.SALES_FLAG available_Sales_Flag,
    NVL(D.TICOM_FLAG,'N') available_TI_Flag,
    A.UPDATE_DATE modified_Date,
    E.PERSON_AID modified_Name,
    NVL(D.OBSOLETE_FLAG,'N') obsolete_Flag,
    NVL(D.TICOM_SUB_SYSTEM,'N')  sub_system_Flag
    FROM MSE_MARKET_SEGMENT_MASTER a,
    MSE_SECTOR_MASTER b,
    MSE_WEB_CATEGORY_MASTER c,
    MSE_EE_MASTER d,
    SEC_PERSON e,
    SEC_APPLICATION_PERSON_ASSOC f,
    SEC_APPLICATION g,
    SEC_ROLE h
    WHERE     a.MARKET_SEGMENT_ID = b.FK_MARKET_SEGMENT_ID(+)
    AND b.SECTOR_ID = c.FK_SECTOR_ID(+)
    AND d.FK_SECTOR_ID(+) = b.SECTOR_ID
    AND e.PERSON_AID = f.FK_PERSON_AID
    AND g.APPLICATION_ID = h.FK_APPLICATION_ID
    AND NVL(A.MARKET_SEGMENT_NAME_UPPER,'X')  LIKE
    NVL2 ('%'||LV_Market_Name||'%',
    '%'||LV_Market_Name||'%',
    NVL('%'||A.MARKET_SEGMENT_NAME_UPPER||'%','X'))
    AND NVL(b.SECTOR_NAME_UPPER,'X')  LIKE
    NVL2 ('%'||LV_Sector_Name||'%',
    '%'||LV_Sector_Name||'%',
    NVL('%'||b.SECTOR_NAME_UPPER||'%','X'))
    AND NVL(D.EEQ_SOL_NAME_UPPER,'X') LIKE
    NVL2 ('%'||LV_End_Equipment_Name||'%',
    '%'||LV_End_Equipment_Name||'%',
    NVL('%'||D.EEQ_SOL_NAME_UPPER||'%','X'))
    AND NVL(C.WEB_CATEGORY_NAME_UPPER,'X') LIKE
    NVL2 ('%'||LV_Web_Category_Name||'%',
    '%'||LV_Web_Category_Name||'%',
    NVL('%'||C.WEB_CATEGORY_NAME_UPPER||'%','X'))
    AND A.UPDATE_USER LIKE
    NVL2 ('%'||pv_modified_Name||'%',
    '%'||PV_modified_Name||'%',
    '%'||A.UPDATE_USER||'%')
    AND TRUNC (A.UPDATE_DATE) =
    NVL2 (pv_modified_Date,
    PV_modified_Date,
    TRUNC (A.UPDATE_DATE))
    AND E.PERSON_AID = LV_USERID
    ORDER BY a.MARKET_SEGMENT_NAME,
    a.MARKET_SEGMENT_ID,
    b.SECTOR_NAME,
    b.SECTOR_ID,
    c.WEB_CATEGORY_NAME,
    c.WEB_CATEGORY_ID,
    D.EEQ_SOL_NAME,
    D.EEQ_SOL_ID;

to this string I am passing a dynamic value from Java Front end. But when I pass the value like 'M_' , the search is not picking the correct value.

Can anyone have solution for this (searching '_')

Thanks IK

Upvotes: 0

Views: 93

Answers (1)

Thilo
Thilo

Reputation: 262814

 select * from Market_data where MARKET_SEGMENT_NAME like '%M_%';

the output is ambigious like (Market_two Market_1 NewTestMarket M_testing2 Market_ONE)

That is because you start your query with a % so that it will match the M anywhere in the string.

Also _ is a wildcard, meaning "any single character" (exactly one).

You want to escape it to match only an underscore.

 select * from Market_data where MARKET_SEGMENT_NAME like 'M$_%' ESCAPE '$';

Upvotes: 4

Related Questions