2

I need to extract a string from string, I use a table and it has dynamic strings continuously update, I would like to extract the last string.

I've tried to use CHARINDEX, PATINDEX, SUBSTRING, but the length of the string isn't fixed. Probably the only alternative is to identify the number and then to extract the next string. Does anybody have a suggestion?

Hector Maya           Supervisión  4  Coecillo
Bulmaro Vieyra        Supervisión  1  Coecillo
Bulmaro Vieyra        Supervisión  1  Coecillo
Edgar Salgado         Supervisión  2  Coecillo
José A Castillo       Supervisión  5  Coecillo
Bulmaro Vieyra        Supervisión  1  Coecillo
Marco Martin Ortiz H. Supervisión  3  Oriente León
Arturo Salazar L.     Supervisión  2  Oriente León
Nancy Pérez G.        Supervisión  4  Oriente León
Arturo Salazar L.     Supervisión  2  Oriente León

What I want the last word:

'Coecillo', 'Oriente León', etc

Bridge
  • 29,818
  • 9
  • 60
  • 82
Ric_hc
  • 279
  • 2
  • 13

1 Answers1

2

Using patindex() and reverse() to take the right() part of the string after the last number.

select LastPart = ltrim(rtrim(right(col,patindex('%[0-9]%',reverse(col))-1)))
from t

rextester demo: http://rextester.com/TLE45990

returns:

+--------------+
|   LastPart   |
+--------------+
| Coecillo     |
| Coecillo     |
| Coecillo     |
| Coecillo     |
| Coecillo     |
| Coecillo     |
| Oriente León |
| Oriente León |
| Oriente León |
| Oriente León |
+--------------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59