Exactly what I needed. Thanks. ________________________________ John Marcum Sr. Desktop Architect Bradley Arant Boult Cummings LLP ________________________________
From: [email protected] [mailto:[email protected]] On Behalf Of Christopher Bodnar Sent: Tuesday, January 21, 2014 10:55 AM To: [email protected] Subject: Re: [mssms] SQL Substring Take a look at this: http://www.vbforums.com/showthread.php?265888-T-SQL-Stripping-Path-From-FileName Christopher Bodnar Enterprise Architect I, Corporate Office of Technology:Enterprise Architecture and Engineering Services Tel 610-807-6459 3900 Burgess Place, Bethlehem, PA 18017 [email protected]<mailto:> [cid:[email protected]] The Guardian Life Insurance Company of America www.guardianlife.com<http://www.guardianlife.com/> From: "Marcum, John" <[email protected]<mailto:[email protected]>> To: "SMS List ([email protected]<mailto:[email protected]>)" <[email protected]<mailto:[email protected]>> Date: 01/21/2014 11:47 AM Subject: [mssms] SQL Substring Sent by: [email protected]<mailto:[email protected]> ________________________________ I am trying to get the folder name from the v_R_User.HomeDirectory0 without the full path. For instance I have \\servername\sharename\jsmith<file:///\\servername\sharename\jsmith> and I just wanna see jsmith. In my Select statement I tried SUBSTRING(v_R_user.homeDirectory0, CHARINDEX('\', v_R_user.homeDirectory0) + 1, LEN(v_R_user.homeDirectory0)) AS [Home Folder] But I still get the entire folder path. What should I change? Here's the entire query: SELECT DISTINCT v_r_system_valid.Netbios_Name0 AS [Computer Name], v_r_system_valid.User_Name0 AS [User Name], v_R_User.homeDirectory0 as [Home Directory], SUBSTRING(v_R_user.homeDirectory0, CHARINDEX('\', v_R_user.homeDirectory0) + 1, LEN(v_R_user.homeDirectory0)) AS [Home Folder], v_R_User.Full_User_Name0 AS [Full User Name], v_R_User.department0 AS [Dept] FROM v_R_System_valid join v_R_user on (v_R_user.user_name0 = substring(v_R_System_Valid.user_name0,charindex('\',v_R_System_Valid.user_name0)+1,len(v_R_System_Valid.user_name0))) ________________________________ John Marcum Sr. Desktop Architect Bradley Arant Boult Cummings LLP ________________________________ ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer. ----------------------------------------- This message, and any attachments to it, may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are notified that any use, dissemination, distribution, copying, or communication of this message is strictly prohibited. If you have received this message in error, please notify the sender immediately by return e-mail and delete the message and any attachments. Thank you. ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer. ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer.
<<inline: image001.jpg>>
