sql - how to pick string and Concatenate from other column -

i have data

declare @t table (id int,val varchar(200),ad varchar(100))  insert @t (id,val,ad)values (1,'d:\documents\franchiseeupload\caste\cgc011400000192\01~hyd bbs 11 jun.pdf','01~hyd bbs 11 jul.pdf'), (1,'d:\documents\franchiseeupload\caste\cgc011400000193\01~cnm_anx3.pdf 01~cnm_anx3.pdf','01~hyd bbs 11 jul.pdf')  select * @t 

i want pick last '\' , in need add data column

output :

id  val ad      1   d:\documents\franchiseeupload\caste\cgc011400000192\01~hyd bbs 11 jul.pdf    01~hyd bbs 11 jul.pdf     1   d:\documents\franchiseeupload\caste\cgc011400000193\01~hyd bbs 11 jul.pdf    01~hyd bbs 11 jul.pdf 

try using left(), charindex(), len() , reverse() functions:

select id,         left(val, len(val) -charindex('\',reverse(val), 0) + 1) + ad val,         ad @t