如何修剪sql中某些字符后的所有内容

I am trying to format the email address in my table by removing everything starting the @. Also I would like to replace the underscore with blank space.

我试图通过删除从@开始的所有内容来格式化我表中的电子邮件地址。另外我想用空格替换下划线。

For example: [e-mail]

例如：[e-mail]

I would like the above email to be changed like this: FirstName LastName

我希望上面的电子邮件更改为：FirstName LastName

Here is my code but this trims everything after the @ and that is what i want. But how can i replace the underscore with blank. I want all in one statement using the update function. How can I do that?

这是我的代码，但这会修剪@之后的所有内容，这就是我想要的。但是如何用空白替换下划线。我想在一个声明中使用更新功能。我怎样才能做到这一点？

SELECT 
     left (Email, CHARINDEX('@',Email)-1)
  FROM [Dashboard]

Thanks for the help

谢谢您的帮助

2 个解决方案

#1

SELECT REPLACE(LEFT(Email, CHARINDEX('@',Email)-1),'_',' ')
FROM [DSR].[dbo].[RCA_Dashboard]

#2

This can be helpful if you need to remove all after the last certain character:

如果您需要在最后一个特定字符后删除所有内容，这会很有用：

Declare @String nvarchar(max) = 
  'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\log.ldf'


select reverse(substring(reverse (@String), CHARINDEX('\', reverse (@String))+1, len(reverse (@String))));