SQL like used to search for a pattern in given input supports with character data only.
It uses 2 special character (Meta characters).
(i) % --->represents zero or more characters.
(ii) – (underscore) represents one character.
Syntax for SQL Like
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
Examples
The following statement List the employees whose name begins with ‘s’
SELECT ename, sal, job
FROM emp
WHERE ename LIKE 's%';
The following statement List the employees whose name ends with ’s’
SELECT ename, sal, job
FROM emp
WHERE ename LIKE '%s';
The following statement List the employees whose name ends with ’S’(Capital),’s’(Small)
SELECT ename, sal, job
FROM emp
WHERE ename LIKE '%s' OR ename LIKE '%S';
The following statement List the employees whose name begins and ends with ‘s’
SELECT ename, sal, job
FROM emp
WHERE ename LIKE 's%s';
The following statement List the employees whose name got 5 letters
SELECT ename, sal, job
FROM emp
WHERE ename LIKE '-----';
The following statement List the employees whose name got 2nd letter as ‘i’
SELECT ename, sal, job
FROM emp
WHERE ename LIKE '_i%';
The following statement List the employees whose name got ‘LL’
SELECT ename, sal, job
FROM emp
WHERE ename LIKE '%LL%';
Searching for Meta characters
Using escape option we can search for Meta characters in given
input.
List the employees whose name got ‘_‘(underscore)
SELECT ename
FROM emp
WHERE ename LIKE '%\_%' ESCAPE '\';
Output
ANIL_KUMAR
KIRAN_KUMAR
SANDEEP_REDDY
List the employees whose name got ‘%’ symbol
SELECT ename, sal, job
FROM emp
WHERE ename LIKE '%\%%' escape '\';
Output
ANIL%KUMAR
KIRAN%KUMAR
SANDEEP%REDDY
SQL NOT Like Operator
Syntax for SQL NOT Like
SELECT column1, column2, ...
FROM table_name
WHERE columnN NOT LIKE pattern;
Examples
The following statement List the employees whose name not begins with ‘s’
SELECT ename, sal, job
FROM emp
WHERE ename NOT LIKE 's%';
The following statement List the employees whose name not ends with ’s’
SELECT ename, sal, job
FROM emp
WHERE ename NOT LIKE '%s';
The following statement List the employees whose name not ends with ’S’(Capital),’s’(Small)
SELECT ename, sal, job
FROM emp
WHERE ename NOT LIKE '%s' OR ename NOT LIKE '%S';
The following statement List the employees whose name not begins and not ends with ‘s’
SELECT ename, sal, job
FROM emp
WHERE ename NOT LIKE 's%s';
Hi There,
ReplyDeleteWhat a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this.
Showing error converting data type varchar to numeric.
This code:
declare @code as varchar(250)
set @code = '8164,8165,8166,8167,8168'
select * from mac_accounts where acc_code in (@code)
Thanks a lot. This was a perfect step-by-step guide. Don’t think it could have been done better.
Merci
Hari
Hi Dayakar,
ReplyDeleteSeems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on SQL Like Operator
I have DMLStatements which contains some polish characters for POLAND country.
When i am executing that DML through the QL Developer i am able to see my Polishcharacters.
but when we gave the release through the SQLPLUS those characters are replacing with some special characters
like ? (Question Mark)
Data type of the column is : NCLOB
NLS_CHARACTERSET : AL32UTF8
NLS_NCHAR_CHARACTERSET : AL16UTF16
Could you please help to resolve this?
THANK YOU!! This saved my butt today, I’m immensely grateful.
Kind Regards,
Irene Hynes
Hello There,
ReplyDelete11/10!! Your blog is such a complete read. I like your approach with SQL Like Operator . Clearly, you wrote it to make learning a cake walk for me.
there are TWO tables as below
USER_DATA_B
1>USER_ID
2>CUST_ID
3>CREATE_DT
4>LOG_ID
USER_LOGON_ID_CHANGE_B
1> ID
2>USER_ID
3>NEW_LOGON_ID
4>OLD_LOGON_ID
5>OLD_CREATE_DT
6>ROW_CREATE_DT
the existing query to show the details in UI is as below
SELECT USER_ID,OLD_CREATE_DT,ROW_CREATE_DT FROM USER_LOGON_ID_CHANGE_B WHERE OLD_LOGON_ID=?
AND NEW_LOGON_ID IS NULL
UNION
SELECT USER_ID,CREATE_DT,NULL FROM USER_DATA_B WHERE LOG_ID=?
ORDER BY OLD_CREATE_DT
Now, my requirement is to join the above query with the following table
CUSTOMER_ATTRIBUTE_B
1>CUST_ID
2> ATTRIBUTE_TYPE_CD
3>ATTRIBUTE_DATA
The new query output should be below
1>USER_ID
2>OLD_CREATE_DT,
3>ROW_CREATE_DT
4> ATTRIBUTE_TYPE_CD
5>ATTRIBUTE_DATA
Great effort, I wish I saw it earlier. Would have saved my day :)
Thank you,
Preethi.
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete