Write a query

Suppose that, I have a table and table contain an email column. email ------- varun.gaur@ gmail.com
                                                                                                                                        alok.varma@gmail.com
                                                                                                                                        sanjeevsharma@gmail.com
                                                                                                                                       lalitchaudhary@gmail.com

Question:-- Write a query to replace even character with hash symbol(#) and each row contains Gmail domain as it is, like this--- v#r#n#g#u#@gmail.com


Please write the best query sir as soon as possible
Varun GaurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Not sure if this is something which can be done on the fly. What you need is a scalar SQL Server function which will do the job. Then call that function in your select statement with column as a parameter value.

Here is the function you can use:

create function [dbo].[MaskMail]
(
  @MailAddress [varchar](1000)
)
returns [varchar](1000)
as
begin

-- select [dbo].[MaskMail]('varun.gaur@ gmail.com')

  declare @atAt integer = charindex('@', @MailAddress);
  declare @domain varchar(1000) = substring(@MailAddress, @atAt, 1000);
  declare @maskThis varchar(1000) = substring(@MailAddress, 1, @atAt - 1);
  declare @maskThisLength integer = len(@maskThis)
  declare @index integer = 1;
  declare @returnValue varchar(1000) = ''
  while(@index < @maskThisLength + 1)
  begin
    if(@index % 2 = 0)
      select @returnValue += '#'
    else
      select @returnValue += substring(@MailAddress, @index, 1)
    select @index += 1;
  end
  return @returnValue + @domain
end
go

select [dbo].[MaskMail]('varun.gaur@ gmail.com')
go

Open in new window

0
Mark WillsTopic AdvisorCommented:
A recursive CTE using the column name of email from a table #yourdata

;with cte_mask as
(
   select email,email as masking, 2 as idx,charindex('@gmail.com',email) - 1 as pos
   from #yourdata
   where email like '%@gmail.com'

   union all

   select d.email,cast(stuff(c.masking,idx,1,'#') as varchar(100)),c.idx+2,c.pos
   from cte_mask C
   inner join #yourdata D on C.email = D.email 
   where c.idx between 2 and c.pos
)
  select email, masking from cte_mask
  where idx > pos

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Varun GaurAuthor Commented:
Thanks all,
                     This is very useful.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Varun GaurAuthor Commented:
Thanks sir,
I really appreciate you, this is very useful.
0
Varun GaurAuthor Commented:
Mark sir,
                Please check your query once. because we can't use outer query alias in the inner query. so your query is not working.
0
Mark WillsTopic AdvisorCommented:
It is certainly working on my machine.

It is a Common Table Expression where you can use the name of the CTE within the CTE. There is no inner / outer query as such.

It starts at ";with ... " and finishes at "where idx > pos"

if needed, create a table #yourtable(email varchar(100))  and populate with email addresses

Are you getting an error ? If so, please advise.
0
Mark WillsTopic AdvisorCommented:
I see you have accepted an answer already - still - want to know what your error was, because it works perfectly on your test data.

And, please, I do know about SQL Server and would NOT post an entry where there is an alias problem / inner/outer query reference problems - which means to me - you didnt even look at it.... So, you can try below and maybe learn something new.

Try running your selected answer with an email address of  "someone.somewhere@outlook.com" and see if that complies with your requirement of "gmail domain" which is why the selected answer returns "'varun.gaur@ gmail.com" and I dont because of the embedded space between @ and gmail

create table #yourdata (email varchar(100))
insert #yourdata values
('varun.gaur@ gmail.com'), 
('alok.varma@gmail.com'), 
('sanjeevsharma@gmail.com'), 
('lalitchaudhary@gmail.com')

-- now run the query

;with cte_mask as
(
   select email,email as masking, 2 as idx,charindex('@gmail.com',email) - 1 as pos
   from #yourdata
   where email like '%@gmail.com'

   union all

   select d.email,cast(stuff(c.masking,idx,1,'#') as varchar(100)),c.idx+2,c.pos
   from cte_mask C
   inner join #yourdata D on C.email = D.email 
   where c.idx between 2 and c.pos
)
  select email, masking from cte_mask
  where idx > pos


/* results

email                       masking
lalitchaudhary@gmail.com    l#l#t#h#u#h#r#@gmail.com
sanjeevsharma@gmail.com     s#n#e#v#h#r#a@gmail.com
alok.varma@gmail.com        a#o#.#a#m#@gmail.com

*/

Open in new window

0
Varun GaurAuthor Commented:
yes, It is working. thanks sir
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.