I want to get the string which is before @ and after any other special character or blank space in SQL Server

user_14202552 0
Viewed 38

I would like to split the string in SQL sever and get the value which is before @ and after any other special character.

Example 1: I have a string     
abc.123.vet@xyz.com     
Expected output: vet

Example 2: aaa@yy.com  
Expected output: aaa

Example 3: aaa bte@yy.com  
Expected output: bte

I have tried:

Code snippet #1:

DECLARE @email VARCHAR(100)
SET @email = 'firstname.lastname@email.com'

SELECT SUBSTRING(@email, 0, CHARINDEX('@', @email))

Code snippet #2:

DECLARE @emai1l VARCHAR(100)
SET @emai1l = 'first.name.lastname@email.com'

SELECT SUBSTRING(SUBSTRING(@emai1l, 0, CHARINDEX('@', @emai1l)), 0, CHARINDEX('.', @emai1l))
sql-server sql split regex
1 Answers

SQL Server is not optimal for this type of string manipulation. However, here is one method:

select t.str, v.str,
       right(v.str,
             patindex('%[, .]%', reverse(v.str) + ' ') - 1
            ) as result
from (values ('abc.123.vet@xyz.com'), ('aaa@yy.com'), ('aaa bte@yy.com')
     ) t(str) cross apply
     (values (left(t.str, charindex('@', t.str) - 1))
     ) v(str) ;

Here is a db<>fiddle.

user_11440350
Related