CODEWITHSUNDEEP
sql-server
Philip
Philip

Reputation: 2628

Complicated SubString to extract String within String

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:

 C0001506
 C0001516
 C0001517
 C0001518
 C0001519
 C0001520

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?

Upvotes: 0

Views: 35

Answers (1)

Ilyes
Ilyes

Reputation: 14928

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

Upvotes: 1

Related Questions