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)

Which DBMS you are using ?

Pரதீப்
– Pரதீப்

2017-07-23 13:31:46 +00:00
Commented Jul 23, 2017 at 13:31 — Pரதீப்
– Pரதீப், Commented Jul 23, 2017 at 13:31
exactly three times? or at least three times?

Charles Bretana
– Charles Bretana

2017-07-23 13:32:31 +00:00
Commented Jul 23, 2017 at 13:32 — Charles Bretana
– Charles Bretana, Commented Jul 23, 2017 at 13:32
both exactly 3 & more than 3 as well...

Prashant Tendulkar
– Prashant Tendulkar

2017-07-23 13:33:16 +00:00
Commented Jul 23, 2017 at 13:33 — Prashant Tendulkar
– Prashant Tendulkar, Commented Jul 23, 2017 at 13:33
And why not like? That is the appropriate way to do this.

Gordon Linoff
– Gordon Linoff

2017-07-23 13:40:59 +00:00
Commented Jul 23, 2017 at 13:40 — Gordon Linoff
– Gordon Linoff, Commented Jul 23, 2017 at 13:40

Pரதீப் · Accepted Answer · 2017-07-23 15:34:12Z

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

edited Jul 23, 2017 at 15:34

answered Jul 23, 2017 at 13:33

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Prashant Tendulkar Over a year ago

can u pls tell me logic

Prashant Tendulkar Over a year ago

i need another help from you

Pரதீப் Over a year ago

@PrashantTendulkar - yes

Prashant Tendulkar Over a year ago

want to write a check constraint(while creating a table) which accepts value between 2 dates like ('25-oct-94' to '10-may-16')

Prashant Tendulkar Over a year ago

create table dob5 ( birthdate date, CONSTRAINT check_dates CHECK (birthdate BETWEEN '25-oct-94' AND '10-may-16') ) this is how i tried @Pரதீப்

|

Charles Bretana · Accepted Answer · 2017-07-23 13:35:16Z

1

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

answered Jul 23, 2017 at 13:35

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

2 Comments

Prashant Tendulkar Over a year ago

can you tell me logic of this ?

Charles Bretana Over a year ago

Yes, if you create a string that has all the 9s removed, then it's length will be the length of the unmodified string minus the number of 9s that were removed.

Collectives™ on Stack Overflow

sql query about string function

2 Answers 2

16 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related