I am new to sql. Please help.
I have a table with column values as below
XYZ_3244H_V65_C005_Z1234567455_P_F_20160127_0137.txt
ERTL_3244H_V65_C005_YIIUUUUUUAB_1P_W_20160316_1227.txt
WTERN_3244H_VTWT05_TWJEONSOSJS_QWT_N_20160316_0937.txt
I want a query which outputs
Z1234567455
YIIUUUUUUAB
TWJEONSOSJS
I need to extract the value between 4th and 5th underscores counted from reverse of the string. Please help. TIA.
Using numbers table you can accomplish this,all you have to do is create numbers table..
declare @string varchar(max)
set @string='_'+'XYZ_3244H_V65_C005_Z1234567455_P_F_20160127_0137.txt'+'_'
select
substring (@string,n+1,charindex('_',@string,n+1)-n-1)
from numbers
where
substring(@string,n,1)='_'
and n<len(@string)
you also can use rownumber to select values in any place like below..
select
substring (@string,n+1,charindex('_',@string,n+1)-n-1),row_number() over (order by charindex('_',@string,n+1)) as rn
from numbers
where
substring(@string,n,1)='_'
and n<len(@string)
Then use a cte to get a number at any place like
;with cte
as
(
select
substring (@string,n+1,charindex('_',@string,n+1)-n-1) as splitstrig,row_number() over (order by charindex('_',@string,n+1)) as rn
from numbers
where
substring(@string,n,1)='_'
and n<len(@string)
)
select * from cte where rn=5
I like functions for such things. If the format changes you have one place to fix it.
Try this
CREATE FUNCTION [dbo].[extract_part](@VAL varchar(max))
returns varchar(16)
as
begin
declare
@idx int,
@split varchar(64),
@result varchar(16),
@count int;
declare
@tbl TABLE (id int identity(1,1), col varchar(64));
select @idx = 1
if len(@VAL )<1 or @VAL is null return null;
while @idx!= 0
begin
set @idx = charindex('_',@VAL)
if @idx!=0
set @split= left(@VAL,@idx - 1)
else
set @split= @VAL
if(len(@split)>0)
insert into @tbl(col) values(@split)
set @VAL= right(@VAL,len(@VAL) - @idx)
if len(@VAL) = 0 break
end
select @count = count(1) from @tbl;
select @result = col from @tbl where id = @count - 4;
return @result
end
select [dbo].[extract_part]('XYZ_3244H_V65_C005_Z1234567455_P_F_20160127_0137.txt')
PS. SQL 2016 comes equipped with a new SPLIT function, which would simplify this a bit.
With use of xml:
DECLARE @xml xml
;WITH cte AS (
SELECT REVERSE(string) as string
FROM (VALUES
('XYZ_3244H_V65_C005_Z1234567455_P_F_20160127_0137.txt'),
('ERTL_3244H_V65_C005_YIIUUUUUUAB_1P_W_20160316_1227.txt'),
('WTERN_3244H_VTWT05_TWJEONSOSJS_QWT_N_20160316_0937.txt')
) AS t(string)
)
SELECT @xml =(
SELECT CAST('<b><a>' +REPLACE(string,'_','</a><a>') + '</a></b>' as xml)
FROM cte
FOR XML PATH ('')
)
SELECT REVERSE(t.v.value('a[5]','nvarchar(20)'))
FROM @xml.nodes('/b') as t(v)
Output:
--------------------
Z1234567455
YIIUUUUUUAB
TWJEONSOSJS
(3 row(s) affected)
In MySQL, you could do it like this.
select substring_index(substring_index(val, '_', -5),'_',1) from
(select 'XYZ_3244H_V65_C005_Z1234567455_P_F_20160127_0137.txt' val) t
