Using select to display part of a field.

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 2000 Forums

SQL Server Development (2000)

Using select to display part of a field.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-07 : 08:22:29

Mike writes "Hello,

I have a SQL 7.0 database that logs web usage. On of the fields in the table
is clientusername and it logs domain\username. I would like to create a view
that selects from clientusername and only display username without the domain.
In MS Access I can use:

Select Mid([clientusername],5) and this will drop our domain name including
the \. How can I do this in SQL?
I need to extract the username so that I can e-mail the usage reports to
the user by appending the @domain.com to the end of their name.

Thank You "

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 08:35:04

Play with this to learn the concept:

select substring('user\domain',1,charindex('\','user\domain',1)-1)
+'@'+
substring('user\domain',charindex('\','user\domain',1)+1,len('user\domain'))

then :

create view ViewName as
select substring(fieldname,1,charindex('\',fieldname,1)-1)
+'@'+
substring(fieldname,charindex('\',fieldname,1)+1,len(fieldname))
from tablename

Edited by - dataphile on 06/07/2002 08:37:18

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources