Clever Geek Handbook

What is the number of occurrences of a character in a SQL string value?

If you have an email address

you.late.you@asdf.com

you_late_you@asdf.com

How can you count if you have 2 dots / underscore using sql code?

+3


source to share


5 answers 


select len('you.late.you@asdf.com') - len(replace('you.late.you@asdf.com', '.', ''))


+6


source


this will give you the desired result.

 DECLARE @str VARCHAR(1000)
SET @str = 'you.l_ate.you@as_df.com'
SELECT (LEN(@str)- LEN(REPLACE(@str ,'.' ,'')))+(LEN(@str)- LEN(REPLACE(@str ,'_' ,'')))


answer: 5

+1


source


I think he wants to count letters without dots before @:

declare @myEmail varchar(50)
set @myEmail = 'you.late.you@asdf.com'

declare @mySearch varchar(50)
set @mySearch = SUBSTRING (@myEmail,0 , PATINDEX( '%@%',@myEmail))
select (LEN(REPLACE(@mySearch, '.', '')))
+1


source


If you want to count the number of lines with two .

or _

, you can do the following:

select count(*) 
  from mytable 
 where left(email, charindex('@', email)) like '%[._]%[._]%'

left

and charindex

are used to ignore the domain name (which will have .

).

Ref: LIKE (TSQL, SQL Server 2008)

0


source 


select 
    length(substr('you.late.you@asdf.com',1,INSTR('you.late.you@asdf.com','@',1))) - 
    length(replace(substr('you.late.you@asdf.com',1,INSTR('you.late.you@asdf.com','@',1)), '.', '')) 
from dual
0


source






More articles:


All Articles