sql query about string function

Question

ID  MOBILE 
1   9869600733 
2   9869600793 
3   9869600799 

all id whose mobile number containing 9 three times(using string functions like replace, substr, etc)... ? (without like , % , etc)

Answer 1

You can use LEN and Replace

Where len(MOBILE)-len(replace(MOBILE ,'9',''))>=3

Note : Some DBMS uses LENGTH instead of LEN

Where length(MOBILE)-length(replace(MOBILE ,'9',''))>=3

• DEMO

---

• replace(MOBILE ,'9','') will replace all the 9's with empty string
• length(MOBILE) will count the number of characters in Mobile column
• length(replace(MOBILE ,'9','')) will count the number of characters in Mobile column as replacing 9's with empty string
• length(MOBILE)-length(replace(MOBILE ,'9','')) here the difference will tell the number of missing characters that is our 9, you can use this difference to count the 9

Answer 2

exactly three '9's:

Select * from mytable
Where len(mobile) - len(replace(mobile, '9', '')) = 3

at least three '9's:

Select * from mytable
Where len(mobile) - len(replace(mobile, '9', '')) >= 3