Obtenga el carácter entre los 2 primeros caracteres especiales en SQL

sql-server substring

Question

Tengo datos en sql (Solo para tener en cuenta: SQL STudio es el IDE ) como:

data
a_10_b_c
a_1_b_c

Quiero obtener los datos entre los dos primeros símbolos _ :

Output
10
1

Answer 1

Este sería mi enfoque:

SELECT CAST(''<x>'' + REPLACE(data,''_'',''</x><x>'') + ''</x>'' AS XML).value(''/x[2]'',''int'')
FROM YourTable

Primero transforma esto en un XML y luego elige el segundo nodo.

EDITAR: Algunos ejemplos más donde este enfoque es útil:

APLICACIÓN CRUZADA: puede usar este enfoque para obtener varios tokens a la vez

DECLARE @tbl TABLE(separated VARCHAR(100));
INSERT INTO @tbl VALUES(''1_23:50_Look_this_is_a_test''),(''2_12:00_that''''s_one_more_test''),(''3_13:30_great!_It_works!'');

SELECT Converted.value(''/x[1]'',''int'') AS number
      ,Converted.value(''/x[2]'',''time'') AS time
      ,Converted.value(''/x[3]'',''varchar(max)'') AS text
FROM @tbl
CROSS APPLY(SELECT CAST(''<x>'' + REPLACE(separated,''_'',''</x><x>'') + ''</x>'' AS XML) AS Converted) AS MySeparated
--type-safe and easy:
/*
number  time    text
1       23:50   Look
2       12:00   that''s
3       13:30   great!
*/
GO

CTE: uso como parámetro

DECLARE @Parameter VARCHAR(100)=''1_12:30_SomeValue'';
WITH MyParameters AS
(
    SELECT CAST(''<x>'' + REPLACE(@Parameter,''_'',''</x><x>'') + ''</x>'' AS XML).value(''/x[1]'',''int'') AS IntParam
          ,CAST(''<x>'' + REPLACE(@Parameter,''_'',''</x><x>'') + ''</x>'' AS XML).value(''/x[2]'',''time'') AS TimeParam
          ,CAST(''<x>'' + REPLACE(@Parameter,''_'',''</x><x>'') + ''</x>'' AS XML).value(''/x[3]'',''varchar(max)'') AS TextParam
)
SELECT IntParam,TimeParam,TextParam
FROM MyParameters
/*
IntParam    TimeParam   TextParam
1           12:30:00    SomeValue
*/
GO

Cadena dividida: Transformar en lista

DECLARE @MyIDs VARCHAR(100)=''3,5,7'';
SELECT A.B.value(''.'',''int'') TheIntValue
FROM(SELECT CAST(''<x>'' + REPLACE(@MyIDs,'','',''</x><x>'') + ''</x>'' AS XML) AS MyListAsXML) AS x
CROSS APPLY MyListAsXML.nodes(''/x'') AS A(B)

/*
TheIntValue
3
5
7
*/
GO

Declaración dinámica de entrada

DECLARE @tbl TABLE(ID INT,Content VARCHAR(max));
INSERT INTO @tbl VALUES(1,''Value 1''),(2,''Value 2''),(3,''Value 3''),(4,''Value 4''),(5,''Value 5''),(6,''Value 6''),(7,''Value 7'');

DECLARE @MyIDs VARCHAR(100)=''3,5,7'';
/*
This won''t work (due to the fact, that @MyIDs is not a list of INTs but a text
SELECT * FROM @tbl WHERE ID IN(@MyIDs)
*/
WITH AsList AS
(
    SELECT A.B.value(''.'',''int'') TheIntValue
    FROM(SELECT CAST(''<x>'' + REPLACE(@MyIDs,'','',''</x><x>'') + ''</x>'' AS XML) AS MyListAsXML) AS x
    CROSS APPLY MyListAsXML.nodes(''/x'') AS A(B)

)
SELECT * FROM @tbl WHERE ID IN(SELECT TheIntValue FROM AsList)

/*
ID  Content
3   Value 3
5   Value 5
7   Value 7
*/

Answer 2

Puede hacer esto con funciones de cadena anidadas. A menudo, esto es más simple usando outer apply :

select t3.output
from t outer apply
     (select stuff(t.col, 1, charindex(''_'', t.col), '''') as col2
     ) t2 outer apply
     (select left(t2.col2, charindex(''_'', t2.col2)) as output
     ) t3;