SQL: Trimming characters from the end of a string

One of the items stored in the SQL database for the Intranet site I’m helping with is the users email address. Users do not type in their full email address when signing up on the site. The stored procedure used to create the SQL record appends “@domain.com” to the end of their network username (all users are on our network so the “@domain.com” is the same). We also wanted it so that the site admins didn’t have to see or type the full email address in if they had to edit the user. The site admins should only see the login name.

Normally, you can pull a substring of a line of text by doing something like:

SELECT SUBSTRING(email_add,1,5) AS email_add FROM Users

That SUBSTRING function tells SQL to start at character one and pull the string up to the fifth character. If the email address “myname@domain.com” were in that table, the result would be “mynam”. That doesn’t work because that wouldn’t be the actual login name. It would work fine if the name was only 5 characters long but the usernames are variables in length. I need to just trim off the “@domain.com” part, which is a constant length.

The first thought might be to use LTRIM or RTRIM. That won’t work because those functions only remove leading or trailing spaces (respectively).

You still have to use the substring function but you also use another function called reverse. Using reverse, the following statement:

SELECT REVERSE(email_add) AS email_add FROM Users

would return the value “moc.niamod@emanym”. I can now trim off the first 11 characters by using the query:

SELECT SUBSTRING(REVERSE(email_add),12,20) AS email_add FROM Users

The query starts at character 12 and pulls up to the next 19 (plus the first character) characters. The result of the query would be “emanym”. That is close but we don’t want a mirror image of the login name so we add another reverse statement:

SELECT REVERSE(SUBSTRING(REVERSE(email_add),12,20)) AS email_add FROM Users

Working from the middle of the query out, the REVERSE in the middle gives us “moc.niamod@emanym”. The SUBSTRING function gives us “emanym”. The final REVERSE spins it back around and gives us “myname”.

I know some ASP guru’s could have written some code to just trim off the characters starting with the “@” symbol. This is just a way to do it on the backend query.