String Manipulation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

String Manipulation

Author

Topic

GoodFella3993
Starting Member

8 Posts

Posted - 2011-09-19 : 10:38:34

i have a table with email addresses. I want to select from this field but only take the first 33 Characters. With this, I only want to select 'COMPLETE' email address after taking the first 33 characters. IE: I don't want "1@Yahoo.c"



create table #test (email nvarchar(255))

INSERT INTO #test (email) values ('1@Yahoo.com, 2@Yahoo.com')
INSERT INTO #test (email) values ('1@Yahoo.com, 2@Yahoo.com, 3@Yahoo.com, 4@Yahoo.com')
INSERT INTO #test (email) values ('1@Yahoo.com, 2@Yahoo.com, 3@Yahoo.com, 4@Yahoo.com, 5@Yahoo.com, 6@Yahoo.com, 7@Yahoo.com')
INSERT INTO #test (email) values ('1@Yahoo.com, 2@Yahoo.com, 3@Yahoo.com, 4@Yahoo.com, 5@Yahoo.com')
INSERT INTO #test (email) values ('1@Yahoo.com, 2@Yahoo.com, 3@Yahoo.com, 4@Yahoo.com, 5@Yahoo.com')
INSERT INTO #test (email) values ('1@a.com, 2@b.com, 3@Yahoo.com, 4@Yahoo.com, 5@Yahoo.com')
INSERT INTO #test (email) values ('1.2@Yahoo.com, 2@Yahoo.com')

select left(email, 33), email from #test

The select returns:

1@Yahoo.com, 2@Yahoo.com
1@Yahoo.com, 2@Yahoo.com, 3@Yahoo
1@Yahoo.com, 2@Yahoo.com, 3@Yahoo
1@Yahoo.com, 2@Yahoo.com, 3@Yahoo
1@Yahoo.com, 2@Yahoo.com, 3@Yahoo
1@a.com, 2@b.com, 3@Yahoo.com, 4@
1.2@Yahoo.com, 2@Yahoo.com

I want the select to return:

1@Yahoo.com, 2@Yahoo.com
1@Yahoo.com, 2@Yahoo.com
1@Yahoo.com, 2@Yahoo.com
1@Yahoo.com, 2@Yahoo.com
1@Yahoo.com, 2@Yahoo.com
1@a.com, 2@b.com, 3@Yahoo.com
1.2@Yahoo.com, 2@Yahoo.com

Any Ideas?

Thanks,
Marc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 11:09:48

[code]
;With cte(email,val,id,maxid)
as
(
select email,val,id,
max(case when patindex('%.co%',val) > 0 then id else null end) over(partition by email)
from
(select left(email, 33) as email from #test )t
cross apply dbo.ParseValues(t.email,',')f
)

select rtrim(ltrim(left(email ,patindex('%' + val + '%',email)+ len(val)-1)))
from cte
where id=maxid

Parsevalues can be found here

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GoodFella3993
Starting Member

8 Posts

Posted - 2011-09-19 : 13:53:03

I have added 2 more columns to the dataset. I wish to have them simply displayed as well. This will be apart of an INSERT statment. I am new to this type of query. Is this possible?

create table #test (email nvarchar(255), c2 nvarchar(11), c3 nvarchar(11))

INSERT INTO #test (email, c2, c3) values ('1@Yahoo.com, 2@Yahoo.com', 'a1', 'a2')
INSERT INTO #test (email, c2, c3) values ('1@Yahoo.com, 2@Yahoo.com, 3@Yahoo.com, 4@Yahoo.com', 'b1', 'b2')
INSERT INTO #test (email, c2, c3) values ('1@Yahoo.com, 2@Yahoo.com, 3@Yahoo.com, 4@Yahoo.com, 5@Yahoo.com, 6@Yahoo.com, 7@Yahoo.com', 'c1', 'c2')
INSERT INTO #test (email, c2, c3) values ('1@Yahoo.com, 2@Yahoo.com, 3@Yahoo.com, 4@Yahoo.com, 5@Yahoo.com', 'd1', 'd2')
INSERT INTO #test (email, c2, c3) values ('1@Yahoo.com, 2@Yahoo.com, 3@Yahoo.com, 4@Yahoo.com, 5@Yahoo.com', 'e1', 'e2')
INSERT INTO #test (email, c2, c3) values ('1@a.com, 2@b.com, 3@Yahoo.com, 4@Yahoo.com, 5@Yahoo.com', 'f1', 'f2')
INSERT INTO #test (email, c2, c3) values ('1.2@Yahoo.com, 2@Yahoo.com', 'g1', 'g2')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 14:11:02

what should be output for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 14:12:36

if you just want them also in end resultset use like


;With cte(email,c2,c3,val,id,maxid)
as
(
select email,c2,c3,val,id,
max(case when patindex('%.co%',val) > 0 then id else null end) over(partition by  email) 
from
(select left(email, 33) as  email,c2, c3 from #test )t
cross apply dbo.ParseValues(t.email,',')f
)

select rtrim(ltrim(left(email ,patindex('%' + val + '%',email)+ len(val)-1))),c2,c3
from  cte 
where id=maxid

Parsevalues can be found here

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GoodFella3993
Starting Member

8 Posts

Posted - 2011-09-19 : 15:06:59

I figured it out doing it like this:


With cte(email,val,id,maxid, c2)
as
(
select email,val,id,
	max(case when patindex('%.co%',val) > 0 then id else null end) over(partition by  email) , c2
from
(select left(email, 33) as  email, c2 from #test )t
cross apply dbo.ParseValues(t.email,',')f
)

select rtrim(ltrim(left(email ,patindex('%' + val + '%',email)+ len(val)-1))), c2
from  cte 
where id=maxid

Next Question: How do I use that as the Select for an INSERT STATEMENT


INSERT INTO #NewTempTable (EmailAddress, C2)
With cte(email,val,id,maxid, c2)
as
(
select email,val,id,
	max(case when patindex('%.co%',val) > 0 then id else null end) over(partition by  email) , c2
from
(select left(email, 33) as  email, c2 from #test )t
cross apply dbo.ParseValues(t.email,',')f
)

select rtrim(ltrim(left(email ,patindex('%' + val + '%',email)+ len(val)-1))), c2
from  cte 
where id=maxid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 15:10:39

it should be


;With cte(email,val,id,maxid, c2)
as
(
select email,val,id,
	max(case when patindex('%.co%',val) > 0 then id else null end) over(partition by  email) , c2
from
(select left(email, 33) as  email, c2 from #test )t
cross apply dbo.ParseValues(t.email,',')f
)

INSERT INTO #NewTempTable (EmailAddress, C2)
select rtrim(ltrim(left(email ,patindex('%' + val + '%',email)+ len(val)-1))), c2
from  cte 
where id=maxid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GoodFella3993
Starting Member

8 Posts

Posted - 2011-09-19 : 15:52:00

Makes Sense. Thanks!

Last question is in regards to performance. How would something like this perform on a LARGE data set?

I have one large working table (20 million records) that I will be joining to temp table that will be used for batching (10,000 records).

I would assume once the join is made restricting the data set to 10,000 ... the overhead shouldnt be too high.

Thanks again,
Marc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 02:41:52

did you try this suggestion against a resonable datset? if yes, can you post the timings

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources