0

I have below file name, how can get characters after 1st, 2nd, and 3rd underscores. For example file name is below:

DECLARE @FileName1 VARCHAR(200) = 'Test_173_M_2018_6_1' 
DECLARE @FileName2 VARCHAR(200) = 'Test_21_M_2018_6_1'

I want this in sql server

sgl
  • 563
  • 1
  • 6
  • 16
  • 2
    What version of SQL server are you using, if it's 2016 or above you can use `STRING_SPLIT`, (https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017) and just split your string on the underscore character. – Ryan Wilson Sep 19 '18 at 13:54
  • And if it's pre-2016, you can use one of the splitter functions available on this site to do the same thing. – Brian Sep 19 '18 at 13:55

1 Answers1

1

If you want columns instead of rows. Perhaps a little XML

I would suggest parsename(), but if you have more than 4 positions, it would fail.

Example

DECLARE @FileName1 VARCHAR(200) = 'Test_173_M_2018_6_1' 

Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
From  (Select Cast('<x>' + replace(@FileName1,'_','</x><x>')+'</x>' as xml) as xDim) as A

Returns

Pos1    Pos2    Pos3    Pos4    Pos5    Pos6    Pos7    Pos8    Pos9
Test    173     M       2018    6       1       NULL    NULL    NULL

If you'd rather a TVF, take a peek at How to extract values from column and update result in another column

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66