Pasar los datos string a una columna

DT,

Necesitas una funcion que parta la frase por palabras.

Ejemplo:

CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT TOP (@n) n AS Number FROM Nums WHERE n <= @n;
GO
CREATE FUNCTION dbo.inline_split_me(@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(
SELECT
ROW_NUMBER() OVER(ORDER BY Number) AS rn,
ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N' ' COLLATE Slovenian_BIN2,
@param + convert(nvarchar(MAX), N' '),
Number) - Number)))) AS Value
FROM dbo.fn_nums(len(@param)) AS Numbers
WHERE substring(convert(nvarchar(MAX), N' ') + @param, Number, 1) = N' '
COLLATE Slovenian_BIN2
)
GO
DECLARE @T TABLE (
libro_id int NOT NULL,
capitulo_id int NOT NULL,
narracion varchar(MAX)
);

INSERT INTO @T VALUES(1, 1, 'La puerta se cerr brscamente');
INSERT INTO @T VALUES(1, 2, 'Era el verano de 1978');
INSERT INTO @T VALUES(2, 1, 'Una silueta pasaba por la noche...');

SELECT
*
FROM
@T AS T
OUTER APPLY
dbo.inline_split_me(T.narracion) AS S
ORDER BY
libro_id,
capitulo_id,
rn
GO
DROP FUNCTION dbo.inline_split_me, dbo.fn_nums;
GO

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html


AMB


"DT" wrote:

Hola a todos
Tengo la isguiente tabla:
libro_id capitulo_id narracion
1 1 "La puerta se cerr brscamente"
1 2 "Era el verano de 1978"
2 1 "Una silueta pasaba por la noche..."

Necesito obtener este resultado:
libro_id capitulo_id palabra_de_Narracin
1 1 La
1 1 puerta
1 1 se
1 1 cerr
1 1 brscamente
1 2 Era
1 2 el
1 2 verano
1 2 de
1 2 1978

Muchsimas gracias por su aporte!!! esto me rompe la cabeza :(
Trabajo con SQL Server 2005

Muchas gracias!
muy útil.


"Alejandro Mesa" wrote in message
news:

DT,

Necesitas una funcion que parta la frase por palabras.

Ejemplo:

CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT TOP (@n) n AS Number FROM Nums WHERE n <= @n;
GO
CREATE FUNCTION dbo.inline_split_me(@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(
SELECT
ROW_NUMBER() OVER(ORDER BY Number) AS rn,
ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N' ' COLLATE Slovenian_BIN2,
@param + convert(nvarchar(MAX), N' '),
Number) - Number)))) AS Value
FROM dbo.fn_nums(len(@param)) AS Numbers
WHERE substring(convert(nvarchar(MAX), N' ') + @param, Number, 1) = N' '
COLLATE Slovenian_BIN2
)
GO
DECLARE @T TABLE (
libro_id int NOT NULL,
capitulo_id int NOT NULL,
narracion varchar(MAX)
);

INSERT INTO @T VALUES(1, 1, 'La puerta se cerr brscamente');
INSERT INTO @T VALUES(1, 2, 'Era el verano de 1978');
INSERT INTO @T VALUES(2, 1, 'Una silueta pasaba por la noche...');

SELECT
*
FROM
@T AS T
OUTER APPLY
dbo.inline_split_me(T.narracion) AS S
ORDER BY
libro_id,
capitulo_id,
rn
GO
DROP FUNCTION dbo.inline_split_me, dbo.fn_nums;
GO

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html


AMB


"DT" wrote:

Hola a todos
Tengo la isguiente tabla:
libro_id capitulo_id narracion
1 1 "La puerta se cerr brscamente"
1 2 "Era el verano de 1978"
2 1 "Una silueta pasaba por la noche..."

Necesito obtener este resultado:
libro_id capitulo_id palabra_de_Narracin
1 1 La
1 1 puerta
1 1 se
1 1 cerr
1 1 brscamente
1 2 Era
1 2 el
1 2 verano
1 2 de
1 2 1978

Muchsimas gracias por su aporte!!! esto me rompe la cabeza :(
Trabajo con SQL Server 2005