sql - texto - ¿Cómo divido una cadena para que pueda acceder al elemento x?
sql substring hasta un caracter (30)
Al usar SQL Server, ¿cómo divido una cadena para poder acceder al ítem x?
Tome una cadena de "Hola John Smith". ¿Cómo puedo dividir la cadena por espacio y acceder al elemento en el índice 1 que debería devolver "John"?
A partir de SQL Server 2016 , string_split
DECLARE @string varchar(100) = ''Richard, Mike, Mark''
SELECT value FROM string_split(@string, '','')
Aquí hay una función que cumplirá el objetivo de la pregunta de dividir una cadena y acceder al elemento X:
CREATE FUNCTION [dbo].[SplitString]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255),
@ElementNumber INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @inp VARCHAR(MAX)
SET @inp = (SELECT REPLACE(@List,@Delimiter,''_DELMTR_'') FOR XML PATH(''''))
DECLARE @xml XML
SET @xml = ''<split><el>'' + REPLACE(@inp,''_DELMTR_'',''</el><el>'') + ''</el></split>''
DECLARE @ret VARCHAR(MAX)
SET @ret = (SELECT
el = split.el.value(''.'',''varchar(max)'')
FROM @xml.nodes(''/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]'') split(el))
RETURN @ret
END
Uso:
SELECT dbo.SplitString(''Hello John Smith'', '' '', 2)
Resultado:
John
Bueno, el mío no es tan simple, pero aquí está el código que utilizo para dividir una variable de entrada delimitada por comas en valores individuales y colocarla en una variable de tabla. Estoy seguro de que podría modificar esto ligeramente para dividir en función de un espacio y luego hacer una consulta SELECT básica contra esa variable de tabla para obtener sus resultados.
-- Create temporary table to parse the list of accounting cycles.
DECLARE @tblAccountingCycles table
(
AccountingCycle varchar(10)
)
DECLARE @vchAccountingCycle varchar(10)
DECLARE @intPosition int
SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + '',''
SET @intPosition = CHARINDEX('','', @vchAccountingCycleIDs, 1)
IF REPLACE(@vchAccountingCycleIDs, '','', '''') <> ''''
BEGIN
WHILE @intPosition > 0
BEGIN
SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))
IF @vchAccountingCycle <> ''''
BEGIN
INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)
END
SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)
SET @intPosition = CHARINDEX('','', @vchAccountingCycleIDs, 1)
END
END
El concepto es prácticamente el mismo. Otra alternativa es aprovechar la compatibilidad con .NET dentro del propio SQL Server 2005. Básicamente, puede escribirse un método simple en .NET que dividiría la cadena y luego lo expondría como una función / procedimiento almacenado.
Esto es algo que hice para obtener un token específico en una cadena. (Probado en MSSQL 2008)
Primero, creando las siguientes funciones: (se encuentran en: aquí
CREATE FUNCTION dbo.SplitStrings_Moden
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
E42(N) AS (SELECT 1 FROM E4 a, E2 b),
cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1)))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
FROM cteStart s;
y
create FUNCTION dbo.getToken
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255),
@Pos int
)
RETURNS varchar(max)
as
begin
declare @returnValue varchar(max);
select @returnValue = tbl.Item from (
select ROW_NUMBER() over (order by (select null)) as id, * from dbo.SplitStrings_Moden(@List, @Delimiter)
) as tbl
where tbl.id = @Pos
return @returnValue
end
entonces puedes usarlo así:
select dbo.getToken(''1111_2222_3333_'', ''_'', 1)
que devuelven 1111
La respuesta de Aaron Bertrand es genial, pero defectuosa. No maneja con precisión un espacio como delimitador (como fue el ejemplo en la pregunta original), ya que la función de longitud elimina los espacios finales.
El siguiente es su código, con un pequeño ajuste para permitir un delimitador de espacio:
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim+''x'')-1) = @Delim
) AS y
);
Puede dividir una cadena en SQL sin necesidad de una función:
DECLARE @bla varchar(MAX)
SET @bla = ''BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C''
-- http://.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT
x.XmlCol.value(''.'', ''varchar(36)'') AS val
FROM
(
SELECT
CAST(''<e>'' + REPLACE(@bla, '','', ''</e><e>'') + ''</e>'' AS xml) AS RawXml
) AS b
CROSS APPLY b.RawXml.nodes(''e'') x(XmlCol);
Si necesita admitir cadenas arbitrarias (con caracteres especiales xml)
DECLARE @bla NVARCHAR(MAX)
SET @bla = ''<html>unsafe & safe Utf8CharsDon''''tGetEncoded ÄöÜ - "Conex"<html>,Barnes & Noble,abc,def,ghi''
-- http://.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT
x.XmlCol.value(''.'', ''nvarchar(MAX)'') AS val
FROM
(
SELECT
CAST(''<e>'' + REPLACE((SELECT @bla FOR XML PATH('''')), '','', ''</e><e>'') + ''</e>'' AS xml) AS RawXml
) AS b
CROSS APPLY b.RawXml.nodes(''e'') x(XmlCol);
Sé que es tarde, pero recientemente tuve este requisito y se me ocurrió el siguiente código. No tengo la opción de usar la función definida por el usuario. Espero que esto ayude.
SELECT
SUBSTRING(
SUBSTRING(''Hello John Smith'' ,0,CHARINDEX('' '',''Hello John Smith'',CHARINDEX('' '',''Hello John Smith'')+1)
),CHARINDEX('' '',''Hello John Smith''),LEN(''Hello John Smith'')
)
Solución basada en conjuntos puros utilizando
TVF
con recursiva
CTE
.
Puede
JOIN
y
APPLY
esta función a cualquier conjunto de datos.
create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))
returns table
as return
with r as (
select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
union all
select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
, left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
, [no] + 1 [no]
from r where value > '''')
select ltrim(x) [value], [no] [index] from r where x is not null;
go
Uso:
select *
from [dbo].[SplitStringToResultSet](''Hello John Smith'', '' '')
where [index] = 1;
Resultado:
value index
-------------
John 1
Un enfoque moderno que utiliza STRING_SPLIT , requiere SQL Server 2016 y superior.
DECLARE @string varchar(100) = ''Hello John Smith''
SELECT
ROW_NUMBER() OVER (ORDER BY value) AS RowNr,
value
FROM string_split(@string, '' '')
Resultado:
RowNr value
1 Hello
2 John
3 Smith
Ahora es posible obtener el elemento n º del número de fila.
¿Qué hay de usar la
string
y la declaración de
values()
?
DECLARE @str varchar(max)
SET @str = ''Hello John Smith''
DECLARE @separator varchar(max)
SET @separator = '' ''
DECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, ''''''),('''''')
SET @str = ''SELECT * FROM (VALUES('''''' + @str + '''''')) AS V(A)''
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
Conjunto de resultados alcanzado.
id item
1 Hello
2 John
3 Smith
Aquí hay un UDF que lo hará. Devolverá una tabla de los valores delimitados, no probé todos los escenarios pero su ejemplo funciona bien.
CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)
--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0
Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))
Insert Into @ReturnTable(part)
Select @part
Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex('' '',@myString,0))
Select @iSpaces = charindex(@deliminator,@myString,0)
end
If len(@myString) > 0
Insert Into @ReturnTable
Select @myString
RETURN
END
GO
Usted lo llamaría así:
Select * From SplitString(''Hello John Smith'','' '')
Edición: Solución actualizada para manejar delimitadores con un len> 1 como en:
select * From SplitString(''Hello**John**Smith'',''**'')
Aquí os dejo una forma sencilla de solución.
CREATE FUNCTION [dbo].[split](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N''<t>'' + REPLACE(@delimited,@delimiter,''</t><t>'') + ''</t>''
INSERT INTO @t(val)
SELECT r.value(''.'',''varchar(MAX)'') as item
FROM @xml.nodes(''/t'') as records(r)
RETURN
END
Ejecuta la función como esta
select * from dbo.split(''Hello John Smith'','' '')
Casi todas las demás respuestas dividen el código y reemplazan la cadena que se divide, lo que desperdicia los ciclos de la CPU y realiza asignaciones de memoria innecesarias.
Cubro una manera mucho mejor de hacer una división de cadenas aquí: http://www.digitalruby.com/split-string-sql-server/
Aquí está el código:
SET NOCOUNT ON
-- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = ''your|string|to|split|here''
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = ''|''
DECLARE @SplitStartPos int = 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
WHILE @SplitEndPos > 0
BEGIN
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
INSERT @SplitStringTable (Value) VALUES (@SplitValue)
SET @SplitStartPos = @SplitEndPos + 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)
SET NOCOUNT OFF
-- You can select or join with the values in @SplitStringTable at this point.
El siguiente ejemplo utiliza un CTE recursivo
Actualización 18.09.2013
CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
(
SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter, @List + @Delimiter)) AS val,
CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '''') AS nvarchar(max)) AS stval,
1 AS [level]
UNION ALL
SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '''') AS nvarchar(max)),
[level] + 1
FROM cte
WHERE stval != ''''
)
INSERT @returns
SELECT REPLACE(val, '' '','''' ) AS val, [level]
FROM cte
WHERE val > ''''
RETURN
END
Demo en SQLFiddle
En mi opinión, ustedes lo están haciendo demasiado complicado. Simplemente crea un UDF CLR y termina con él.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
public partial class UserDefinedFunctions {
[SqlFunction]
public static SqlString SearchString(string Search) {
List<string> SearchWords = new List<string>();
foreach (string s in Search.Split(new char[] { '' '' })) {
if (!s.ToLower().Equals("or") && !s.ToLower().Equals("and")) {
SearchWords.Add(s);
}
}
return new SqlString(string.Join(" OR ", SearchWords.ToArray()));
}
};
Esta pregunta no es sobre un enfoque de división de cadenas , sino sobre cómo obtener el elemento nth .
Todas las respuestas aquí están haciendo algún tipo de división de cadenas usando recursión,
CTE
,
CHARINDEX
múltiple,
REVERSE
y
PATINDEX
, inventando funciones, métodos de llamada CLR, tablas de números,
CROSS APPLY
CRUZADA ... La mayoría de las respuestas cubren muchas líneas de código.
Pero, si realmente no desea nada más que un enfoque para obtener el elemento nth , esto se puede hacer como una sola línea real , no UDF, ni siquiera una sub-selección ... Y como un beneficio adicional: escriba seguro
Consigue la parte 2 delimitada por un espacio:
DECLARE @input NVARCHAR(100)=N''part1 part2 part3'';
SELECT CAST(N''<x>'' + REPLACE(@input,N'' '',N''</x><x>'') + N''</x>'' AS XML).value(''/x[2]'',''nvarchar(max)'')
Por supuesto
, puede usar variables
para delimitador y posición (use
sql:column
para recuperar la posición directamente del valor de una consulta):
DECLARE @dlmt NVARCHAR(10)=N'' '';
DECLARE @pos INT = 2;
SELECT CAST(N''<x>'' + REPLACE(@input,@dlmt,N''</x><x>'') + N''</x>'' AS XML).value(''/x[sql:variable("@pos")][1]'',''nvarchar(max)'')
Si su cadena puede incluir
caracteres prohibidos
(especialmente uno entre
&><
), todavía puede hacerlo de esta manera.
Simplemente use
FOR XML PATH
en su cadena primero para reemplazar todos los caracteres prohibidos con la secuencia de escape apropiada implícitamente.
Es un caso muy especial si, además, su delimitador es el punto y coma . En este caso, sustituyo el delimitador primero por ''# DLMT #'', y finalmente lo reemplazo por las etiquetas XML:
SET @input=N''Some <, > and &;Other äöü@€;One more'';
SET @dlmt=N'';'';
SELECT CAST(N''<x>'' + REPLACE((SELECT REPLACE(@input,@dlmt,''#DLMT#'') AS [*] FOR XML PATH('''')),N''#DLMT#'',N''</x><x>'') + N''</x>'' AS XML).value(''/x[sql:variable("@pos")][1]'',''nvarchar(max)'');
ACTUALIZACIÓN para SQL-Server 2016+
Lamentablemente, los desarrolladores olvidaron devolver el índice de la pieza con
STRING_SPLIT
.
Pero, utilizando SQL-Server 2016+, hay
JSON_VALUE
y
OPENJSON
.
Con
JSON_VALUE
podemos pasar la posición como la matriz del índice.
Para
OPENJSON
la
documentation
establece claramente:
Cuando OPENJSON analiza una matriz JSON, la función devuelve los índices de los elementos en el texto JSON como claves.
Una cadena como
1,2,3
no necesita más que corchetes:
[1,2,3]
.
Una cadena de palabras como
this is an example
debe ser
["this","is","an","example"]
.
Estas son operaciones de cadena muy fáciles.
Solo pruébalo
DECLARE @str VARCHAR(100)=''Hello John Smith'';
DECLARE @position INT = 2;
--We can build the json-path ''$[1]'' using CONCAT
SELECT JSON_VALUE(''["'' + REPLACE(@str,'' '',''","'') + ''"]'',CONCAT(''$['',@position-1,'']''));
--Vea esto para un divisor de cadenas seguro para la posición ( basado en cero ):
SELECT JsonArray.[key] AS [Position]
,JsonArray.[value] AS [Part]
FROM OPENJSON(''["'' + REPLACE(@str,'' '',''","'') + ''"]'') JsonArray
En
este post
probé varios enfoques y descubrí que
OPENJSON
es realmente rápido.
Incluso mucho más rápido que el famoso método "delimitedSplit8k ()" ...
Estaba buscando la solución en la red y el siguiente trabajo para mí. Ref .
Y llamas a la función así:
SELECT * FROM dbo.split(''ram shyam hari gopal'','' '')
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(8000))
AS
BEGIN
DECLARE @idx INT
DECLARE @slice VARCHAR(8000)
SELECT @idx = 1
IF len(@String)<1 OR @String IS NULL RETURN
WHILE @idx!= 0
BEGIN
SET @idx = charindex(@Delimiter,@String)
IF @idx!=0
SET @slice = LEFT(@String,@idx - 1)
ELSE
SET @slice = @String
IF(len(@slice)>0)
INSERT INTO @temptable(Items) VALUES(@slice)
SET @String = RIGHT(@String,len(@String) - @idx)
IF len(@String) = 0 break
END
RETURN
END
Este patrón funciona bien y se puede generalizar.
Convert(xml,''<n>''+Replace(FIELD,''.'',''</n><n>'')+''</n>'').value(''(/n[INDEX])'',''TYPE'')
^^^^^ ^^^^^ ^^^^
nota CAMPO , INDICE Y TIPO .
Deje alguna tabla con identificadores como
sys.message.1234.warning.A45
sys.message.1235.error.O98
....
Entonces, puedes escribir
SELECT Source = q.value(''(/n[1])'', ''varchar(10)''),
RecordType = q.value(''(/n[2])'', ''varchar(20)''),
RecordNumber = q.value(''(/n[3])'', ''int''),
Status = q.value(''(/n[4])'', ''varchar(5)'')
FROM (
SELECT q = Convert(xml,''<n>''+Replace(fieldName,''.'',''</n><n>'')+''</n>'')
FROM some_TABLE
) Q
Partiendo y fundiendo todas las partes.
La mayoría de las soluciones aquí se utilizan en bucles o CTE recursivos. Un enfoque basado en conjuntos será superior, lo prometo:
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
) AS y
);
Más sobre funciones divididas, por qué (y prueba de ello) mientras que los bucles y los CTE recursivos no se escalan, y mejores alternativas, si se dividen cadenas provenientes de la capa de aplicación:
- Divide las cuerdas de la manera correcta, o la mejor forma de hacerlo.
- Dividir cuerdas: un seguimiento
- Dividir cadenas: ahora con menos T-SQL
- Comparación de métodos de división de cadenas / concatenación
- Procesando una lista de enteros: mi enfoque
- División de una lista de enteros: otro resumen
- Más sobre la división de listas: delimitadores personalizados, prevención de duplicados y mantenimiento del orden
- Eliminar duplicados de cadenas en SQL Server
Sin embargo, en SQL Server 2016 o superior, debes mirar
STRING_SPLIT
y
STRING_AGG()
:
- Sorpresas y suposiciones de rendimiento: STRING_SPLIT ()
- STRING_SPLIT () en SQL Server 2016: seguimiento # 1
- STRING_SPLIT () en SQL Server 2016: Seguimiento # 2
- SQL Server v.Siguiente: STRING_AGG () performance
- Resuelva problemas antiguos con las nuevas funciones STRING_AGG y STRING_SPLIT de SQL Server.
No creo que SQL Server tenga una función de división incorporada, por lo que, aparte de un UDF, la única otra respuesta que conozco es secuestrar la función PARSENAME:
SELECT PARSENAME(REPLACE(''Hello John Smith'', '' '', ''.''), 2)
PARSENAME toma una cadena y la divide en el carácter de punto. Toma un número como su segundo argumento, y ese número especifica qué segmento de la cadena devolver (trabajando desde atrás hacia adelante).
SELECT PARSENAME(REPLACE(''Hello John Smith'', '' '', ''.''), 3) --return Hello
El problema obvio es cuando la cadena ya contiene un punto. Todavía creo que usar un UDF es la mejor manera ... ¿Alguna otra sugerencia?
Otra parte más de la parte de la cadena por delimitador:
create function GetStringPartByDelimeter (
@value as nvarchar(max),
@delimeter as nvarchar(max),
@position as int
) returns NVARCHAR(MAX)
AS BEGIN
declare @startPos as int
declare @endPos as int
set @endPos = -1
while (@position > 0 and @endPos != 0) begin
set @startPos = @endPos + 1
set @endPos = charindex(@delimeter, @value, @startPos)
if(@position = 1) begin
if(@endPos = 0)
set @endPos = len(@value) + 1
return substring(@value, @startPos, @endPos - @startPos)
end
set @position = @position - 1
end
return null
end
y el uso:
select dbo.GetStringPartByDelimeter (''a;b;c;d;e'', '';'', 3)
que devuelve:
c
Primero, cree una función (usando CTE, la expresión de tabla común elimina la necesidad de una tabla temporal)
create function dbo.SplitString
(
@str nvarchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 zeroBasedOccurance,
substring(
@str,
a,
case when b > 0 then b-a ELSE 4000 end)
AS s
from tokens
)
GO
Luego, utilícelo como cualquier tabla (o modifíquelo para que quepa dentro de su proceso almacenado existente) de esta manera.
select s
from dbo.SplitString(''Hello John Smith'', '' '')
where zeroBasedOccurance=1
Actualizar
La versión anterior fallaría para la cadena de entrada de más de 4000 caracteres. Esta versión se encarga de la limitación:
create function dbo.SplitString
(
@str nvarchar(max),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
cast(1 as bigint),
cast(1 as bigint),
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 ItemIndex,
substring(
@str,
a,
case when b > 0 then b-a ELSE LEN(@str) end)
AS s
from tokens
);
GO
El uso sigue siendo el mismo.
Prueba esto:
CREATE function [SplitWordList]
(
@list varchar(8000)
)
returns @t table
(
Word varchar(50) not null,
Position int identity(1,1) not null
)
as begin
declare
@pos int,
@lpos int,
@item varchar(100),
@ignore varchar(100),
@dl int,
@a1 int,
@a2 int,
@z1 int,
@z2 int,
@n1 int,
@n2 int,
@c varchar(1),
@a smallint
select
@a1 = ascii(''a''),
@a2 = ascii(''A''),
@z1 = ascii(''z''),
@z2 = ascii(''Z''),
@n1 = ascii(''0''),
@n2 = ascii(''9'')
set @ignore = ''''''"''
set @pos = 1
set @dl = datalength(@list)
set @lpos = 1
set @item = ''''
while (@pos <= @dl) begin
set @c = substring(@list, @pos, 1)
if (@ignore not like ''%'' + @c + ''%'') begin
set @a = ascii(@c)
if ((@a >= @a1) and (@a <= @z1))
or ((@a >= @a2) and (@a <= @z2))
or ((@a >= @n1) and (@a <= @n2))
begin
set @item = @item + @c
end else if (@item > '''') begin
insert into @t values (@item)
set @item = ''''
end
end
set @pos = @pos + 1
end
if (@item > '''') begin
insert into @t values (@item)
end
return
end
Pruébalo así:
select * from SplitWordList(''Hello John Smith'')
Puede aprovechar una tabla de números para hacer el análisis de cadena.
Crea una tabla de números físicos:
create table dbo.Numbers (N int primary key);
insert into dbo.Numbers
select top 1000 row_number() over(order by number) from master..spt_values
go
Crear tabla de prueba con 1000000 filas
create table #yak (i int identity(1,1) primary key, array varchar(50))
insert into #yak(array)
select ''a,b,c'' from dbo.Numbers n cross join dbo.Numbers nn
go
Crear la función
create function [dbo].[ufn_ParseArray]
( @Input nvarchar(4000),
@Delimiter char(1) = '','',
@BaseIdent int
)
returns table as
return
( select row_number() over (order by n asc) + (@BaseIdent - 1) [i],
substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
from dbo.Numbers
where n <= convert(int, len(@Input)) and
substring(@Delimiter + @Input, n, 1) = @Delimiter
)
go
Uso (genera 3mil filas en 40s en mi laptop)
select *
from #yak
cross apply dbo.ufn_ParseArray(array, '','', 1)
limpiar
drop table dbo.Numbers;
drop function [dbo].[ufn_ParseArray]
El rendimiento aquí no es sorprendente, pero llamar a una función en una tabla de un millón de filas no es la mejor idea. Si realizo una cadena dividida en muchas filas, evitaría la función.
Puede encontrar la solución en la función definida por el usuario de SQL para analizar una cadena delimitada útil (de The Code Project ).
Puedes usar esta lógica simple:
Declare @products varchar(200) = ''1|20|3|343|44|6|8765''
Declare @individual varchar(20) = null
WHILE LEN(@products) > 0
BEGIN
IF PATINDEX(''%|%'', @products) > 0
BEGIN
SET @individual = SUBSTRING(@products,
0,
PATINDEX(''%|%'', @products))
SELECT @individual
SET @products = SUBSTRING(@products,
LEN(@individual + ''|'') + 1,
LEN(@products))
END
ELSE
BEGIN
SET @individual = @products
SET @products = NULL
SELECT @individual
END
END
Sé que es una pregunta antigua, pero creo que alguien puede beneficiarse de mi solución.
select
SUBSTRING(column_name,1,CHARINDEX('' '',column_name,1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX('' '',column_name,1)+1,LEN(column_name))
,1
,CHARINDEX('' '',SUBSTRING(column_name,CHARINDEX('' '',column_name,1)+1,LEN(column_name)),1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX('' '',column_name,1)+1,LEN(column_name))
,CHARINDEX('' '',SUBSTRING(column_name,CHARINDEX('' '',column_name,1)+1,LEN(column_name)),1)+1
,LEN(column_name))
from table_name
Ventajas:
- Separa todas las 3 sub-cadenas deliminador por ''''.
- Uno no debe usar while loop, ya que disminuye el rendimiento.
- No es necesario girar, ya que toda la subcadena resultante se mostrará en una fila
Limitaciones:
- Uno debe saber el total no. de espacios (subcadena).
Nota : la solución puede dar subcadena hasta N.
Para superar la limitación podemos utilizar la siguiente ref .
Pero, de nuevo, la ref anterior no se puede usar en una tabla (Actaully no pude usarla).
De nuevo, espero que esta solución pueda ayudar a alguien.
Actualización:
en caso de registros> 50000, no es
aconsejable
utilizar
LOOPS
ya que degradará el
rendimiento
Si su base de datos tiene un nivel de compatibilidad de 130 o superior, puede usar la función STRING_SPLIT junto con las cláusulas OFFSET FETCH para obtener el elemento específico por índice.
Para obtener el artículo en el índice N (basado en cero), puede utilizar el siguiente código
SELECT value
FROM STRING_SPLIT(''Hello John Smith'','' '')
ORDER BY (SELECT NULL)
OFFSET N ROWS
FETCH NEXT 1 ROWS ONLY
Para verificar el nivel de compatibilidad de su base de datos , ejecute este código:
SELECT compatibility_level
FROM sys.databases WHERE name = ''YourDBName'';
Utilizo la respuesta de frederic pero esto no funcionó en SQL Server 2005
Lo modifiqué y estoy usando
select
with
union all
y funciona
DECLARE @str varchar(max)
SET @str = ''Hello John Smith how are you''
DECLARE @separator varchar(max)
SET @separator = '' ''
DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, '''''' UNION ALL SELECT '''''')
SET @str = '' SELECT '''''' + @str + '''''' ''
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
Y el conjunto de resultados es:
id item
1 Hello
2 John
3 Smith
4 how
5 are
6 you
SOLUCIÓN SENCILLA PARA PERMITIR EL NOMBRE PRIMERO Y ÚLTIMO
DECLARE @Name varchar(10) = ''John Smith''
-- Get First Name
SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX('' '', @Name)))
-- Get Last Name
SELECT SUBSTRING(@Name, (SELECT CHARINDEX('' '', @Name)) + 1, LEN(@Name))
En mi caso (y en muchos otros, parece ...), tengo una lista de nombres y apellidos separados por un solo espacio. Esto se puede usar directamente dentro de una declaración de selección para analizar el nombre y apellido.
-- i.e. Get First and Last Name from a table of Full Names
SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX('' '', FullName))) as FirstName,
SUBSTRING(FullName, (SELECT CHARINDEX('' '', FullName)) + 1, LEN(FullName)) as LastName,
From FullNameTable
Alter Function dbo.fn_Split
(
@Expression nvarchar(max),
@Delimiter nvarchar(20) = '','',
@Qualifier char(1) = Null
)
RETURNS @Results TABLE (id int IDENTITY(1,1), value nvarchar(max))
AS
BEGIN
/* USAGE
Select * From dbo.fn_Split(''apple pear grape banana orange honeydew cantalope 3 2 1 4'', '' '', Null)
Select * From dbo.fn_Split(''1,abc,"Doe, John",4'', '','', ''"'')
Select * From dbo.fn_Split(''Hello 0,"&""&&&&'', '','', ''"'')
*/
-- Declare Variables
DECLARE
@X xml,
@Temp nvarchar(max),
@Temp2 nvarchar(max),
@Start int,
@End int
-- HTML Encode @Expression
Select @Expression = (Select @Expression For XML Path(''''))
-- Find all occurences of @Delimiter within @Qualifier and replace with |||***|||
While PATINDEX(''%'' + @Qualifier + ''%'', @Expression) > 0 AND Len(IsNull(@Qualifier, '''')) > 0
BEGIN
Select
-- Starting character position of @Qualifier
@Start = PATINDEX(''%'' + @Qualifier + ''%'', @Expression),
-- @Expression starting at the @Start position
@Temp = SubString(@Expression, @Start + 1, LEN(@Expression)-@Start+1),
-- Next position of @Qualifier within @Expression
@End = PATINDEX(''%'' + @Qualifier + ''%'', @Temp) - 1,
-- The part of Expression found between the @Qualifiers
@Temp2 = Case When @End < 0 Then @Temp Else Left(@Temp, @End) End,
-- New @Expression
@Expression = REPLACE(@Expression,
@Qualifier + @Temp2 + Case When @End < 0 Then '''' Else @Qualifier End,
Replace(@Temp2, @Delimiter, ''|||***|||'')
)
END
-- Replace all occurences of @Delimiter within @Expression with ''</fn_Split><fn_Split>''
-- And convert it to XML so we can select from it
SET
@X = Cast(''<fn_Split>'' +
Replace(@Expression, @Delimiter, ''</fn_Split><fn_Split>'') +
''</fn_Split>'' as xml)
-- Insert into our returnable table replacing ''|||***|||'' back to @Delimiter
INSERT @Results
SELECT
"Value" = LTRIM(RTrim(Replace(C.value(''.'', ''nvarchar(max)''), ''|||***|||'', @Delimiter)))
FROM
@X.nodes(''fn_Split'') as X(C)
-- Return our temp table
RETURN
END