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] The Guardian Life Insurance Company of America www.guardianlife.com From: "Marcum, John" <[email protected]> To: "SMS List ([email protected])" <[email protected]> Date: 01/21/2014 11:47 AM Subject: [mssms] SQL Substring Sent by: [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 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.
<<image/jpeg>>
