Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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
0
qjump
Asked:
qjump
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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)
0
 
LIONKINGCommented:
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.
0
 
CrashmanCommented:
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

0
 
Patrick MatthewsCommented:
BTW, the above approaches will all throw errors if you have any values without a # character.  Two ways to guard against that:

UPDATE tbl
SET col = LEFT(col, CHARINDEX('#', col) - 1)
WHERE col LIKE '%#%

Open in new window


or:

UPDATE tbl
SET col = LEFT(col, CHARINDEX('#', col + '#',) - 1)

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now