Seperate the character, special character, numbers from an email id

Question

Hi guys back with one more interesting question.

suppose we have a email id like this:-

Adventure2008Works.DW@microsoft.com

So now the question is to split all those character, special character, numbers and show them in three separate columns shown as below.

Character                       | Special Character | Numbers
_____________________________________________________________
AdventureWorksDWmicrosoftcom    |  .@.              | 2008

score 1 · Answer 1 · answered Apr 08 '14 at 06:47

1

I would create CLR wrapper over .NET RegEx class. Here it is an example.

answered Apr 08 '14 at 06:47

bjnr

3,353
1
18
32

score 1 · Accepted Answer · answered Apr 08 '14 at 07:40

Here is the function:

create function f_tst
(
@txt nvarchar(1000)
) returns table
as
return (with x as
(
select case when substring(@txt, number, 1) like '[a-zA-Z]' then substring(@txt, number, 1)
else '' end a,
case when substring(@txt, number, 1) like '[0-9]' then substring(@txt, number, 1)
else '' end b,
case when substring(@txt, number, 1) NOT like '[a-zA-Z0-9]' then substring(@txt, number, 1)
else '' end c
 from
master..spt_values
where type = 'P'
and number < len(@txt)
)

select distinct( select [a] 
        from x t1 
        for xml path(''), type 
    ).value('.', 'varchar(max)') [Character],
    ( select [c] 
        from x t1 
        for xml path(''), type 
    ).value('.', 'varchar(max)') [Special Character] ,
    ( select [b] 
        from x t1 
        for xml path(''), type 
    ).value('.', 'varchar(max)') [Numbers] 
from x t) 

go

You can test single line like this:

 select * from f_tst('Adventure2008Works.DW@microsoft.com')

You can get the columns from a table like this:

declare @t table(txt nvarchar(1000))
insert @t values
('Adventure2008Works.DW@microsoft.com'),
('Adventure2008Wo12ks.DW@"t...')

select * from @t cross apply dbo.f_tst(txt)

Result:

txt                                 Character                Special Character Numbers
Adventure2008Works.DW@microsoft.com AdventureWorksDWmicrosoftco .@.            2008
Adventure2008Wo12ks.DW@"t...        AdventureWoksDWt            .@"..         200812

score 0 · Answer 3 · edited May 23 '17 at 12:05

You can try this one,

DECLARE @strAlphaNumericMain VARCHAR(256)
DECLARE @strAlphaNumeric VARCHAR(256)
DECLARE @Expr1 VARCHAR(255), @Expr2 VARCHAR(255), @Expr3 VARCHAR(255)
DECLARE @OnlyCharacter VARCHAR(MAX),@OnlyNumber VARCHAR(MAX),@OnlySplChar VARCHAR(MAX) 

SET @strAlphaNumericMain = 'Adventure2008Works.DW@microsoft.com'
SET @strAlphaNumeric = 'Adventure2008Works.DW@microsoft.com'
SET @Expr1 =  '%['+'@.'+']%'
SET @Expr2 =  '%['+'a-z0-9'+']%'
SET @Expr3 =  '%['+'^0-9'+']%'

WHILE PatIndex(@Expr1, @strAlphaNumeric) > 0
SET @strAlphaNumeric = Stuff(@strAlphaNumeric, PatIndex(@Expr1, @strAlphaNumeric), 1, '')
SET @OnlyCharacter = @strAlphaNumeric

SET @strAlphaNumeric = @strAlphaNumericMain
WHILE PatIndex(@Expr2, @strAlphaNumeric) > 0
SET @strAlphaNumeric = Stuff(@strAlphaNumeric, PatIndex(@Expr2, @strAlphaNumeric), 1, '')
SET @OnlyNumber = @strAlphaNumeric

SET @strAlphaNumeric = @strAlphaNumericMain
WHILE PatIndex(@Expr3, @strAlphaNumeric) > 0
SET @strAlphaNumeric = Stuff(@strAlphaNumeric, PatIndex(@Expr3, @strAlphaNumeric), 1, '')
SET @OnlySplChar = @strAlphaNumeric

SELECT @strAlphaNumericMain, @OnlyCharacter,@OnlyNumber,@OnlySplChar

Main source: here

Seperate the character, special character, numbers from an email id

3 Answers3