How to remove characters Before @

Transact-SQL
1

Hi Experts,

I want to update ColA

ColA: value is MSSQLSERVER@Jackson5 for ex
(And The value is sometimes something other than mssqlserver)

To derive a new value of Jackson5.

I've tried TRIM which is of course not the correct option.
Can I use REPLACE to do this ?
Thanks for any tips.

2

SELECT STUFF(ColA, 1, CHARINDEX('@', ColA), '')

2 Likes
3

Ah "STUFF" that's the keyword I was reaching for. Thanks!

4

hi

hope this helps

Another 2 ways of doing this .. LEFT , RIGHT

declare @Temp table ( string varchar(200)) insert into @Temp select 'MSSQLSERVER@Jackson5'

SELECT RIGHT(string, len(string)-CHARINDEX('@', string)) from @Temp

SELECT reverse(LEFT(reverse(string), CHARINDEX('@', reverse(string)) -1)) from @Temp

image

5

Every time you want to do some kind of string manipulation task then I would suggest taking a quick look at the documentation and seeing what each of the functions do. You can then start to formulate an idea as to how you can achieve your goal.

I would give you a link directly to the page you need but I tried that on another forum and after my first post I got banned. I will give you the Google search 'sql server string functions'. That should bring up the Microsoft page with all the details on the string functions you can explore for this issue and any future issues.

The string functions that you will find useful when manipulating email addresses are:

PATINDEX ( '%pattern%' , expression )
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
REVERSE ( string_expression )
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) 
LEN ( string_expression ) 
REPLACE ( string_expression , string_pattern , string_replacement )
SUBSTRING ( expression, start, length )
STUFF ( character_expression , start , length , replace_with_expression )