Dynamic SQL using "LIKE" ---ERROR: "SQL COMMAND NOT PROPERLY ENDED"

Try this one..

AND SUBSTR (c.map, 1, 3) LIKE '%' || p12 || '%'

SQL Server T-SQL does not have a SUBSTR function, it's called SUBSTRING instead.

If you are trying to select records that contain "p12" in the c.map field, no matter where, try the first code snippet.  The % stands for any bunch of characters.

If your search string "p12" should really be the first 3 characters of the c.map field then try snippet 2.  No need to use LIKE because you always have 3 characters returned from the SUBSTRING().

--snippet 1
AND c.map LIKE '%p12%'
 
--snippet 2
AND SUBSTRING(c.map, 1, 3) = 'p12'

Select allOpen in new window

dynamic sql, and within oracle:

 AND SUBSTR (c.map, 1, 3) LIKE ''%'' ||p12|| ''%'' 

Select allOpen in new window

same result sa rrjegan17 :-)

AND SUBSTR (c.map, 1, 3) LIKE '%' ||p12|| '%'

Select allOpen in new window

as the sql is part of dynamic sql, for example:

EXECUTE IMMEDIATE ' select ... where ... '

the suggestions with single quotes won't work...

If its dynmaic sql then try the following...
v_sql := q'[SELECT * FROM yourtable WHERE .... SUBSTR (c.map, 1, 3) LIKE '%' || p12 || '%']';

No....I have tried all the above ways and still can't get the correct solution...Plz help its very urgent

Also please not that p12  is my input parameter passed to the Stored procedure

Angel,
   Since you are caring about the first three characters only, why it cant be like this:

AND c.map LIKE p12 || '%'

<< Also please not that p12  is my input parameter passed to the Stored procedure >>

What is the parameter data type declared for this p12?
If it is varchar2() then you wont be facing any issue, I guess..

>Angel,
>   Since you are caring about the first three characters only, why it cant be like this:

that depends on the needs.

say c.map contains "ABC000000"

and p12 passed contains "C"

it might still require a match...

Hello...i think i got the answer

Thank you very much...in solving this....

Angel, Got it..
I assumed that the asker will be passing three characters as input and hence the confusion.