June 4, 2018 at 12:20 am
Hi, I have one quessiton in sql server .I want remove extension dates in sql server
FileName
-------------------------+---
c:\abc_20181008.txt
c:\xyz_20181007.dat
c:\abc_xyz_20181007.dat
c:\abc_t_W_20180526.CTL
c:\eeFC.CTL
c:\ab.tr_20190101.txt
Based on above data I want output like below :
Table: emp
FileName
-------------------
c:\abc.txt
c:\xyz.dat
c:\abc_xyz.dat
c:\abc_t_W.txt
c:\eeFC.CTL
c:\ab.tr.txt
I have tried like this:
declare @t table (a varchar(50))
insert into @t values ('c:\abc_20181008.txt')
insert into @t values ('c:\xyz_20181007.dat')
insert into @t values ('c:\abc_xyz_20181007.dat')
insert into @t values ('c:\abc_t_W_20180526.CTL')
insert into @t values ('c:\eeFC.CTL')
insert into @t values ('c:\ab.tr_20190101.txt')
select replace(SUBSTRING(a,1,CHARINDEX('2',a) - 1) + SUBSTRING(a,len(a)-3,LEN(a)),'_.','.') from @t
June 4, 2018 at 1:11 am
Assuming your dates always have the _ before them the following will work
declare @t table (a varchar(50))
insert into @t values ('c:\abc_20181008.txt')
insert into @t values ('c:\xyz_20181007.dat')
insert into @t values ('c:\abc_xyz_20181007.dat')
insert into @t values ('c:\abc_t_W_20180526.CTL')
insert into @t values ('c:\eeFC.CTL')
insert into @t values ('c:\ab.tr_20190101.txt')
select a
, case
when pos.dot_pos > 0
and pos.underscore_pos > 0
then reverse(stuff(reverse(a), pos.dot_pos + 1, pos.underscore_pos - pos.dot_pos, ''))
else a
end
from @T
outer apply (select charindex('.', reverse(a)) as dot_pos
, charindex('_', reverse(a)) as underscore_pos
) pos
June 4, 2018 at 2:37 am
An alternative, without having to do a (possibly costly) double REVERSE:
SELECT a,
CASE V.PI WHEN 0 THEN a
ELSE STUFF(a,V.PI,9,'') END AS new_a
FROM @t t
CROSS APPLY(VALUES(PATINDEX('%[_][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',t.a))) V(PI);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply