My table contain column [File] with names of files

I have files like :

U_1456789_23456789_File1_automaticrepair

U_3456789_3456789_File2_jumpjump

B_1134_445673_File3_plane

I_111345_333345_File4_chupapimonienio

P_1156_3556_File5 idk what

etc...

I want to create column where i will see only bolded values, how i can do that ?

CodePudding user response：

If your RDBMS supports it, a regular expression is a much cleaner solution. If it doesn't, (and SQL Server doesn't by default) you can use a combination of SUBSTRING and CHARINDEX to get the text in the column between the second and third underscores as explained in this question.

Assuming a table created as follows:

CREATE TABLE [Files] ([File] NVARCHAR(200));
INSERT INTO [Files] VALUES 
('U_1456789_23456789_File1_automaticrepair'),
('U_3456789_3456789_File2_jumpjump'),
('B_1134_445673_File3_plane'),
('I_111345_333345_File4_chupapimonienio'),
('P_1156_3556_File5 idk what');

You can use the query:

SELECT [File],
  SUBSTRING([File], 
            
            -- Start taking after the second underscore
            -- in the original field value
            CHARINDEX('_', [File], CHARINDEX('_', [File])   1)   1,  

            -- Continue taking for the length between the
            -- index of the second and third underscores
            CHARINDEX('_', [File], CHARINDEX('_', [File], CHARINDEX('_', [File])   1)   1) - (CHARINDEX('_', [File], CHARINDEX('_', [File])   1)   1)) AS Part
FROM [Files];

To get the results:

See the SQL Fiddle

Edit: to brute force support for inputs with only two underscores:

CREATE TABLE [Files] ([File] NVARCHAR(200));
INSERT INTO [Files] VALUES 
('U_1456789_23456789_File1_automaticrepair'),
('U_3456789_3456789_File2_jumpjump'),
('B_1134_445673_File3_plane'),
('I_111345_333345_File4_chupapimonienio'),
('P_1156_3556_File5 idk what'),
('K_25444_filenamecar');

Add a case for when a third underscore could not be found and adjust the start position/length passed to SUBSTRING.

SELECT [File],
  CASE WHEN CHARINDEX('_', [File], CHARINDEX('_', [File], CHARINDEX('_', [File])   1)   1) = 0
    THEN
      SUBSTRING([File],
                CHARINDEX('_', [File])   1,
                CHARINDEX('_', [File], CHARINDEX('_', [File])   1) - (CHARINDEX('_', [File])   1))
    ELSE
      SUBSTRING([File], 
                CHARINDEX('_', [File], CHARINDEX('_', [File])   1)   1,  
                CHARINDEX('_', [File], CHARINDEX('_', [File], CHARINDEX('_', [File])   1)   1) - (CHARINDEX('_', [File], CHARINDEX('_', [File])   1)   1))
    END AS Part
FROM [Files];

See the SQL Fiddle

Note that this approach is even more brittle and you're definitely in the realm of problem that is likely better handled in application code instead of by the SQL engine.