Complicated SubString to extract String within String

Question

I have a table with thousands of rows like the below (fake names):

C:\James\AAA(1) - C0001506 Mrs Michaela Hysell.pdf
C:\James\AAA(10) - C0001516 Mrs Delfina Cardoza.pdf
C:\James\AAA(11) - C0001517 Mrs Melony Pickle.pdf
C:\James\AAA(12) - C0001518 Mr Homer Guillot.pdf
C:\James\AAA(13) - C0001519 Mrs Lawerence Matis.pdf
C:\James\AAA(14) - C0001520 Mrs Yahaira Hausner.pdf

What I'm needing to do is pull out the codes so I have a listing of:

So far I have this:

select SUBSTRING ( FileName ,PatIndex('%-%', FileName + '5'),8)
from [FileNames]

which returns me "- C00015" for example.

Anyone have any ideas how to make this Substring work?

If possible, would be good if it could be guaranteed to pick it up correctly. — Philip
– Philip, Commented Nov 3, 2017 at 1:33

Ilyes · Accepted Answer · 2017-11-03 02:05:30Z

1

This will do the job:

DECLARE @T TABLE ( STR VARCHAR(MAX));

INSERT INTO @T VALUES
('C:\James\AAA(1) - C0001506 Mrs Michaela Hysell.pdf'),
('C:\James\AAA(10) - C0001516 Mrs Delfina Cardoza.pdf'),
('C:\James\AAA(11) - C0001517 Mrs Melony Pickle.pdf'),
('C:\James\AAA(12) - C0001518 Mr Homer Guillot.pdf'),
('C:\James\AAA(13) - C0001519 Mrs Lawerence Matis.pdf'),
('C:\James\AAA(14) - C0001520 Mrs Yahaira Hausner.pdf');

SELECT *, 
       SUBSTRING(STR, CHARINDEX('-', STR, 0)+1, CHARINDEX('-', STR, 0)-8 ) AS Result
FROM @T

Results:

+--------------------------------------------------------+------------+
|                          STR                           |   Result   |
+--------------------------------------------------------+------------+
| C:\James\AAA(1) - C0001506 Mrs Michaela Hysell.pdf     |  C0001506  |
| C:\James\AAA(10) - C0001516 Mrs Delfina Cardoza.pdf    |  C0001516  |
| C:\James\AAA(11) - C0001517 Mrs Melony Pickle.pdf      |  C0001517  |
| C:\James\AAA(12) - C0001518 Mr Homer Guillot.pdf       |  C0001518  |
| C:\James\AAA(13) - C0001519 Mrs Lawerence Matis.pdf    |  C0001519  |
| C:\James\AAA(14) - C0001520 Mrs Yahaira Hausner.pd222f |  C0001520  |
+--------------------------------------------------------+------------+

Demo

Update:

Here is another way to do it using LEFT(), PATINDEX() and SUBSTRING() functions:

SELECT *, 
       LEFT(SUBSTRING(STR, PATINDEX('%- %', STR) + 2, LEN(STR) ), 8) AS Result
FROM @T

edited Nov 3, 2017 at 2:05

answered Nov 3, 2017 at 1:41

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Philip Over a year ago

Thanks for that Sami :)

Ilyes Over a year ago

@James Glad to help :).

Collectives™ on Stack Overflow

Complicated SubString to extract String within String

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related