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;