Return a set number of characters after a symbol

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Return a set number of characters after a symbol

Author

Topic

JasonSQL
Starting Member

8 Posts

Posted - 2012-01-10 : 05:01:52

Hi All

I'm having problems with returning 6 characters after a specific group of characters in an email address

data_sample column
fnam1.lnam1@ZX-Z12345.com
fnam2.lnam2-smith@ZX-Z12346.com

There will always be the same number of characters after the '@'. The '@ZX-' part is consistent too. I want to return a list that shows:

Z12345
Z12346
...

This is what I have that seems to work but there must be a better way

SUBSTRING(RIGHT(data_sample, CHARINDEX('-',REVERSE(data_sample),1) ), 2, 6) AS 'A BIT COMPLEX'

Any help appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 05:05:26

SUBSTRING (data_sample,PATINDEX('%@ZX-%',data_sample)+4,6)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JasonSQL
Starting Member

8 Posts

Posted - 2012-01-10 : 12:00:41

Thanks for the speedy reply visakh16

I also had

SUBSTRING(data_sample, CHARINDEX('@GP-',data_sample,1)+4, 6)

after a bit more help

JasonSQL
Starting Member

8 Posts

Posted - 2012-01-10 : 12:01:47

Sorry '@ZX-' not '@GP-'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 12:33:45

quote:
Originally posted by JasonSQL

Thanks for the speedy reply visakh16

I also had

SUBSTRING(data_sample, CHARINDEX('@GP-',data_sample,1)+4, 6)

after a bit more help

ok ..glad that you sorted it out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources