CODEWITHSUNDEEP
sqlsql-server
nihal idiot
nihal idiot

Reputation: 35

How to take or consider only limited text when user entered in sql?

Can anyone help me on a small requirement. I did much R&D but could not find my solution. The problem is, in SQL, how to take a limited text from whole text when user enters ? ex: If user entered [email protected], I want only "john" & "@abc.com" has to be replaced with NULL in SQL. Can anyone help me on this please.

Upvotes: 0

Views: 57

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44336

When the third parameter in SUBSTRING or the second parameter in LEFT is negative, you will get an error.

With this syntax you don't risking getting an error when the @ doesn't exists, it will return nothing if there is no @:

SELECT SUBSTRING('[email protected]', 0, charindex('@','[email protected]'))

A different way of handling this issue using LEFT. This will return the whole text if there is no @ in the text:

SELECT LEFT('[email protected]', charindex('@','[email protected]' + '@') - 1)

Result:

john

Upvotes: 1

d_luffy_de
d_luffy_de

Reputation: 967

DECLARE @User nvarchar(50) = '[email protected]'

SELECT DISTINCT
         STUFF(@User, CHARINDEX('@', @User), len(@User ), '') AS [User]

Is your question only for EmailID's or for other scenario's as well??

Upvotes: 2

Abhishek
Abhishek

Reputation: 2490

try the below code, this will give you the name part and replace everything else with NULL - 

create table #email (emailid varchar(20))
insert into #email
select '[email protected]' union
select '[email protected]'
select * from #email
select  substring(emailid,1,patindex('%@%',emailid)-1),
replace(emailid,substring(emailid,patindex('%@%',emailid)+1,(patindex('%.%',emailid)-1)-patindex('%@%',emailid)),NULL) 
from #email

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

I guess the optimal solution will be to store emails as they are and when you show just extract name part only

declare @email varchar(100)
    set @email='[email protected]'
    select substring(@email,1,charindex('@',@email)-1) as name_only

Upvotes: 0

Related Questions