Courion Tip – T-SQL string parsing

Here’s the scenario… I have to generate SNC user names from the user profile table. I was told that it starts with a p:, and that the domain part of the email address HAS to be upper case. So, for instance

dummy.user@example.com would have an SNC name of p:dummy.user@EXAMPLE.COM

Here’s the custom macro I came up with:

select 'p:'+
LEFT(EMAIL_ADDRESS,CHARINDEX('@',EMAIL_ADDRESS))+
UPPER(RIGHT(EMAIL_ADDRESS,LEN(EMAIL_ADDRESS)-CHARINDEX('@',EMAIL_ADDRESS)))
from PROFILE
where EMP_ID =

Advertisements

Posted July 28, 2011 by mmdmurphy in Courion Tip, SQL, tips

Tagged with

%d bloggers like this: