February 26, 2007 at 12:01 pm
Table_A col_a = \server1\joeblow, janedoe, \server2\jerrydoe
Table_B col_b = joeblow, janedoe, jerrydoe
I want to join on these 2 tables where the col_a = col_b but I only want the 'real' user id. In other words, instead of col_a = col_b (\server1\joeblow = joeblow), I would have joeblow = joeblow. So, in Table_A, col_a, I want to strip the \serverx\ portion out.
February 26, 2007 at 1:30 pm
I think the real answer would be to keep that information stripped out in another column, but the below code will strip it out, here as an example using a variable, but same logic shouls apply. The case statement is too handle when there is no \ in the name. The commented out line with the reverse in it could be used instead if there are no lines without a \ in it.
And yes I am sure there are better ways to do this, but this has worked for me in the past, to strip domain names out.
Hope this helps
declare
@username varchar(50)
select
@username = '\server2\jerrydoe'
--select @username = 'jerrydoe'
--select @username = reverse(substring(reverse(@username),1, (charindex('\',(reverse(@username))))-1))
select
@username = reverse(substring(reverse(@username),1, case --(charindex('\',(reverse(@username))))-1)
when charindex('\',(reverse(@username))) = 0 then len(@username)
else charindex('\',(reverse(@username)))-1
end
))
select
@username
February 27, 2007 at 3:02 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply