Extract VARCHAR inside another VARCHAR in SQL Server

Question

Extract VARCHAR inside another VARCHAR in SQL Server

Navigation

#1 by (5 votes)
#2 by (2 votes)

3

My question is very simple but requires a bit of programming, easy for some, complex for me at this time. I have a table that gives me these examples as an example:

+--------------------------------------------------+
|                      Datos                       |
+--------------------------------------------------+
| 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     |
+--------------------------------------------------+

My need is to extract from each one of them, the last string ie 'Coecillo' , 'Oriente León' , 'Oriente Sur' , etc.

I tried to extract it with CHARTINDEX , SUBSTRING and PATINDEX , however as you can notice, it does not have the same extension and not the same length. For that reason it has complicated me a lot.

Now, I also have to tell you that the VARCHAR is dynamic, therefore they can not be fixed. Until now my idea is to use the number as a separator, because that number may not change.

sql sql-server

asked by Ric_hc 26.04.2017 в 17:27

source

2 answers

2

With PatIndex you should get the position of the number:

PATINDEX('%[0-9]%', cadenaString)

and then do a substring starting in the index that returns the previous:

SUBSTRING ( cadenaString , PATINDEX('%[0-9]%', cadenaString)+2 , len(cadenaString))

Leaving:

Hector Maya Supervisión 4 Coecillo

As:

Coecillo

answered by 26.04.2017 в 17:48

LEFT OUTER JOIN query with 4 tables What is the best collation for my MySQL Database?

score 5 · Accepted Answer

I found the solution, with the function SUBSTRING , implementing PATINDEX with a REGEX and the code would look like this:

CREATE TABLE #tabla
    ([valor] varchar(48))
;

INSERT INTO #tabla
    ([valor])
VALUES
    ('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')
;

select 
     valor
    ,LEFT(valor, PATINDEX('%[0-9]%', valor) - 1) 
from #tabla

CREATE TABLE #tabla
    ([valor] varchar(48))
;

INSERT INTO #tabla
    ([valor])
VALUES
    ('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')
;

select 
     valor
    ,LEFT(valor, PATINDEX('%[0-9]%', valor) - 1) 
    ,SUBSTRING(valor, PATINDEX('%[0-9]%', valor), 1 ) 
    ,SUBSTRING(valor, PATINDEX('%[0-9]%', valor) +1 , len(valor)) 
from #tabla

I hope you find it helpful, greetings.

Following the suggestions in the comments I add code:

example in dbfiddle

Results, using the page ASCII table :

+--------------------------------------------------+------------------------------------+------------------+------------------+
|                      valor                       |          (No column name)          | (No column name) | (No column name) |
+--------------------------------------------------+------------------------------------+------------------+------------------+
| Hector Maya Supervisión 4 Coecillo               | Hector Maya Supervisión            |                4 |  Coecillo        |
| Bulmaro Vieyra Supervisión 1 Coecillo            | Bulmaro Vieyra Supervisión         |                1 |  Coecillo        |
| Bulmaro Vieyra Supervisión 1 Coecillo            | Bulmaro Vieyra Supervisión         |                1 |  Coecillo        |
| Edgar Salgado Supervisión 2 Coecillo             | Edgar Salgado Supervisión          |                2 |  Coecillo        |
| José A Castillo Supervisión 5 Coecillo           | José A Castillo Supervisión        |                5 |  Coecillo        |
| Bulmaro Vieyra Supervisión 1 Coecillo            | Bulmaro Vieyra Supervisión         |                1 |  Coecillo        |
| Marco Martin Ortiz H. Supervisión 3 Oriente León | Marco Martin Ortiz H. Supervisión  |                3 |  Oriente León    |
| Arturo Salazar L. Supervisión 2 Oriente León     | Arturo Salazar L. Supervisión      |                2 |  Oriente León    |
| Nancy Pérez G. Supervisión 4 Oriente León        | Nancy Pérez G. Supervisión         |                4 |  Oriente León    |
| Arturo Salazar L. Supervisión 2 Oriente León     | Arturo Salazar L. Supervisión      |                2 |  Oriente León    |
| Hector Maya Supervisión 4 Coecillo               | Hector Maya Supervisión            |                4 |  Coecillo        |
| Bulmaro Vieyra Supervisión 1 Coecillo            | Bulmaro Vieyra Supervisión         |                1 |  Coecillo        |
| Bulmaro Vieyra Supervisión 1 Coecillo            | Bulmaro Vieyra Supervisión         |                1 |  Coecillo        |
| Edgar Salgado Supervisión 2 Coecillo             | Edgar Salgado Supervisión          |                2 |  Coecillo        |
| José A Castillo Supervisión 5 Coecillo           | José A Castillo Supervisión        |                5 |  Coecillo        |
| Bulmaro Vieyra Supervisión 1 Coecillo            | Bulmaro Vieyra Supervisión         |                1 |  Coecillo        |
| Marco Martin Ortiz H. Supervisión 3 Oriente León | Marco Martin Ortiz H. Supervisión  |                3 |  Oriente León    |
| Arturo Salazar L. Supervisión 2 Oriente León     | Arturo Salazar L. Supervisión      |                2 |  Oriente León    |
| Nancy Pérez G. Supervisión 4 Oriente León        | Nancy Pérez G. Supervisión         |                4 |  Oriente León    |
| Arturo Salazar L. Supervisión 2 Oriente León     | Arturo Salazar L. Supervisión      |                2 |  Oriente León    |
+--------------------------------------------------+------------------------------------+------------------+------------------+