MSSQL Qry - Tiny bit o' help?

[sanpei]

I am running this qry:
SELECT * FROM [ShopMgt].[SM].[Customers] WHERE EmailAddress LIKE '%*%'

I want to remove the * in the EmailAddress field when it returns a result for *

Any help on the correct query?

Google is just confusing me.

 

[PTNL]

So you want to return matching rows that have an asterisk in the "EmailAddress" field, but you want the data formatted so that the asterisk is removed from the result set... Am I understanding this correctly? If so, then the REPLACE() function can help or some more advanced substringing work as well.

Though I am curious: Why is it important to not return the email address as it was stored in the database? (An asterisk is a legal character.)

 

[sanpei]

Yes, you are getting it right.

It needs to be removed because it is getting kicked back from the mail server.

An employee was using the * as a personal notation tool.

 

[renixinq]

Do you want the * removed from the field in the database or just in the output?

PTNL is correct you can just use replace on the output to remove the *, something like replace(field,'*','') would do it.

If you are wanting to remove it from the database you'll need to write an update statement like update table set email = replace(field,'*','') where email like '%*%'.

If doing an update statement test somewhere else before you update your production database.

 

[Zangmonkey]

What data do you want to be returned?

 

[sanpei]

example@email.com*

to

example@email.com

 

[Zangmonkey]

example@email.com*

to

example@email.com

is the asterisk always at the end?

 

[sanpei]

Yes

 

[Zangmonkey]

do you want to just SELECT the data? Or do you want to update the data to the new value?

 

[sanpei]

update to new would be ideal.

example@email.com* to example@email.com

thank you for your help

 

[Zangmonkey]

UPDATE
    [ShopMgt].[SM].[Customers]
SET
    [EmailAddress] = REPLACE([EmailAddress], '*', '')
WHERE
    RIGHT([EmailAddress],1) = '*'

This will remove ANY asterisk, not just ones on the right side, if the EmailAddress ends with an asterisk
If you just want to remove the one on the right side, and leave any in the middle, do this:

UPDATE
    [ShopMgt].[SM].[Customers]
SET
    [EmailAddress] = LEFT([EmailAddress], LEN([EmailAddress] - 1))
WHERE
    RIGHT([EmailAddress],1) = '*'

I haven't tested these, so maybe back up before executing.

 

[sanpei]

Thank you!

Used the first one and it was perfect!!