SQL: How to split a column of type varchar?
I have a table that has a colum开发者_如何学编程n named 'languages', but it has the following types of values :
english; polish; portuguese;
.. etc.
I want to split so I can insert it in another table as:
english
polish
portugese
And go on.
I already searched in Google and find this split function:
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
I already tested it with this :
SELECT * FROM dbo.Split(' ', 'I hate bunnies')
So I tried to adapt this to my case :
INSERT INTO labbd11..language(language) SELECT s FROM dbo.Split(';', disciplinabd..movies.languages)
Then it gives me this exception:
The multi-part identifier "disciplinabd..movies.languages" could not be bound. Severity 16
Any ideas ?
Best regards, Valter Henrique.
Use CROSS APPLY
INSERT INTO labbd11..language(language)
SELECT DISTINCT s.s
FROM disciplinabd..movies m
CROSS APPLY dbo.Split(';', m.languages) S
But if I read your query correctly, you are splitting the languages from ALL movies, and inserting the resultant languages from the movie into the language table (1 column only). Hope this is a test query, otherwise it has no business merit at all.
精彩评论