asked on 1/8/2013

SQL Server - Text field update

Hi,

I have a table of approx 1000 records in SQL Server 2008 and I need to update a text field containing email addresses. I need to remove all text to the right of, and including, the first # symobol and retain the left hand part of the address.

account@bsigroup.com#mailto:account@bsigroup.com#

Thanks

Microsoft SQL Server 2008 Microsoft SQL Server 2005 Microsoft SQL Server

Last Comment

Patrick Matthews

1/8/2013

Steve Wales

1/8/2013

Try something like this:

Update yourtable
set email_address = substring('account@bsigroup.com#mailto:account@bsigroup.com#', 1, charindex('#', 'account@bsigroup.com#mailto:account@bsigroup.com#')-1)

Open in new window

(Playing with using a variable instead of hard coding the string twice, but it isn't working yet, but thought I'd get this your way for starters)

Member_2_861731

1/8/2013

You can try something like this:

UPDATE yourTable
SET yourField = SUBSTRING(yourField,1,CHARINDEX('#',yourField)-1)

To test first, you can try a simple select:

SELECT yourField, SUBSTRING(yourField,1,CHARINDEX('#',yourField)-1)

And adjust accordingly.

Hope it helps.

Haver Ramirez

1/8/2013

here

DECLARE @tbl TABLE (field VARCHAR(150))

insert INTO @tbl (field) VALUES ('account@bsigroup.com#mailto:account@bsigroup.com#')

SELECT field FROM @tbl t

UPDATE @tbl SET field = LEFT(field,CHARINDEX('#',field)-1) 

SELECT field FROM @tbl t

Open in new window

ASKER CERTIFIED SOLUTION

Patrick Matthews

1/8/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial