Current user query

[ElTech]

I have a query that requires that the current user's name = the username in the database
when I query "select user" I get "domain\username"

how can I tell my query that the username is the last 7 characters of the user result

 

[SQLSister]

Use the right function to pick out only the last 7 characters

Select right(fieldname,7) from table1

 

[MDXer]

ElTech,

What if usernames are less or greater than 7 characters does this need to be taken into account?


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[ElTech]

If I run SELECT right(user,7) I get the correct output

User
JohnHill

The following is not working:

select transaction from order where user=right('user',7)

0 results

select transaction from order where user=JohnHill

10 results

 

[MDXer]

You need to place the right on the column definition like in the following

select transaction from order where right(user,7)= 'JohnHill'

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[ElTech]

We made the usernames all equal 7 characters

first 4 letters of first name and first 3 of last name
Replacing last letter with numbers if duplicates will exist

Examples: John Hill username=JohnHil
If we hire another JohnHill then username=JohnHi2

 

[MDXer]

ooops the above won't work either because JohnHill is 8 characters. Which bring me to my first question.


What if usernames are less or greater than 7 characters does this need to be taken into account?


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[mnk82o]

This should work
Select ltrim(right((' '+ltrim(rtrim(isnull(fieldname,' ')))),7)) user from table1

Note: donot forget to give seven spaces between two quotes

MNK

 

[MDXer]

mnk82o,

while your method works,

Select SubString(UserName,CharIndex('\',UserName)+1,Len(UserName)) from testUser

is probably cleaner and slightly more robust.

I know this point is now moot but thought this might shed some light on any future issues.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck