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 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