CODEWITHSUNDEEP
sqlsql-servert-sqlwildcard
user4970927
user4970927

Reputation: 179

Wildcarding for email in SQL Server

This is the sample email data in table

2.3|[[email protected]]

Below is a query that I have been working on for a little while:

SELECT * FROM WS_TRANSACTION
WHERE SERVICE_ID = 'GETUSERTOKENLOGIN' 
       AND SERVICE_ID LIKE '%@%'

What this is supposed to do is give me the data from this table, and make the column service_id only contain emails, like the johnnyappleseed email. Is there a wild card out there for SQL that will delete that useless stuff so that it only portrays the emails, or do I have to go about this in a different way?

I tried to get the email by using the percent signs, but I believe that its deleting everything before and after the @ which is not what I want.

Any help would be very appreciative

Upvotes: 0

Views: 130

Answers (3)

TLaV
TLaV

Reputation: 389

IF length of the characters at the beginning and the end of the string are always the same, you could use LEFT and RIGHT functions, along with the LEN function like this.

SELECT LEFT(RIGHT(SERVICE_ID,LEN(SERVICE_ID)-5),LEN(SERVICE_ID)-6)

Upvotes: 0

lookslikeanevo
lookslikeanevo

Reputation: 565

if your columns with email are automatically logged with the data you represented, and you only want the email portion use:

replace(replace(SUBSTRING('2.3 | [[email protected]]', (charindex( '[','2.3 | [[email protected]]')), LEN('2.3 | [[email protected]]')),'[',''),']','')

output is

[email protected]

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

I think you can get the Substring to get the email, As you said you have Email wrap in square brackets '[ ]', So use SubString()

Declare @a varchar(max)
SET @a = '2.3|[[email protected]]'
SELECT  SUBSTRING(@a,CharIndex('[',@a)+1,(CharIndex(']',@a)-CharIndex('[',@a))-1);

SEE DEMO

Upvotes: 4

Related Questions