Selecting between [text] brackets

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

General SQL Server Forums

New to SQL Server Programming

Selecting between [text] brackets

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-22 : 07:47:10

Hello everyone.

Is there a way i can select email addresses that are like

[sqlteam@sqlteam.com]

i want to select between the brackets.

Regards

Rob

MCTS / MCITP certified

Kristen
Test

22859 Posts

Posted - 2011-09-22 : 08:21:25

If they are the first and last characters then:


DECLARE	@MyString varchar(1000)
SELECT	@MyString = '[sqlteam@sqlteam.com]'
SELECT SUBSTRING(@MyString, 2, LEN(@MyString)-2)

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-22 : 08:24:08

thank you for your reply

what about if i have a list of emails.

do i just replace the email with column name ie

SELECT @MyString = email

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-22 : 08:29:15

also what if i have names before the email

for example

joe.bloggs[joe.bloggs@sqlteam.com] all i want to extract is the address

Kristen
Test

22859 Posts

Posted - 2011-09-22 : 08:52:31

So your data might be

aaa[bbb@ccc.ddd]eee

where aaa and eee are optional?

Do you have multiple email addresses in a single data value like this:

aaa[bbb@ccc.ddd]eee, fff[ggg@hhh.iii]jjj

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-22 : 08:59:54

its ok ive sorted it.

thank you for your help my solution was this.

I would have multiple emails but with the names in front of them

ie joe.bloggs[joe.bloggs@sqlteam.com]

update bifmemail

set columnName = (SELECT REPLACE(STUFF([ColumnName],1,CHARINDEX('[',[columnName]),''),']',''))
where [columnName] like '%@%'

select * Table

Kristen
Test

22859 Posts

Posted - 2011-09-22 : 09:07:30

It won't handle any additional characters after the "]" - but you may not have any in your data?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-22 : 09:16:34

how would i amend for characters after the ']'

can you put your addition in red please.

Kristen
Test

22859 Posts

Posted - 2011-09-22 : 09:29:52

You can use the same method using STUFF() to remove the characters following "]" (i.e. like you did for the characters up to "]")

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-22 : 09:32:40

yes but what about to go into one statement

Kristen
Test

22859 Posts

Posted - 2011-09-22 : 09:34:42

Shouldn't be a problem. Just make sure the right-hand-one is done before the left-hand-one.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-22 : 10:14:49

ok thank you for your help. much appreciated

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-23 : 04:01:17

If all emails are wrapped by square braces, you can do these too

select substring(email,2,len(email)-2) from table
select replace(replace(email,'[',''),']','') from table

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-09-23 : 06:34:12

Madhi:

needs to accomodate

joe.bloggs[joe.bloggs@sqlteam.com]

too - and I think it would be wise to also handle:

joe.bloggs[joe.bloggs@sqlteam.com]xxxxxx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-23 : 06:54:22

This is one option


select stuff(email,CHARINDEX(']',email),LEN(email),'') from
(
	select stuff(email,1,charindex('[',email),'') as email from
	(
		select 'joe.bloggs[joe.bloggs@sqlteam.com]' as email union all
		select 'joe.bloggs[joe.bloggs@sqlteam.com]xxxxxx' union all
		select '[sqlteam@sqlteam.com]'
	) as t
) as t

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-09-23 : 07:00:43

This would do me I think? Important to handle the Right Hand Ones first



SELECT STUFF(STUFF(
	email
	, CHARINDEX(']', email), LEN(email), '')
	, 1, CHARINDEX('[',email), '')
FROM
(
	select 'joe.bloggs[joe.bloggs@sqlteam.com]' as email union all
	select 'joe.bloggs[joe.bloggs@sqlteam.com]xxxxxx' union all
	select '[sqlteam@sqlteam.com]'
) as t

It would not handle

jo]e.bloggs[joe.bloggs@sqlteam.com]xxxxxx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-26 : 10:58:52

Yes. Good stuff

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources