I have a table that holds words and their translations.
in the translation col I have a few options of translation separated by comma.
I have been told here by yosiasz that I have to change it.
I want to have 1 table for the words and an additional table for the translations
what is the best way to take the word id and the data in the translation col and to insert it to the new translation table.
eg..
if I have in a row
5 | myWord | trans1,trans2,trans3
I want to insert it to the new translation table as 3 rows
5 | trans1
5 | trans2
5 | trans3
(afterward I will delete the translation row from the words table)
hi
you can use
string_split function
or
hope it helps
1 Like
My db is 2008R2
I tried a few solution that are given there.
but I didn't succeed.
here id a demo table
I'll be glad if you would help me with writing the query.
CREATE TABLE [dbo].[try1](
[word] [nvarchar](50) NULL,
[trans] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT try1 select 'a', '10,85,100'
INSERT try1 select 'b', '50,17'
INSERT try1 select 'c', '6,2,11'
thanks
Eli
how does one know what language is 10, 85, 100 associated with?
CREATE TABLE [dbo].[try1](
[word] [nvarchar](50) NULL,
[trans] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT try1 select 'a', '10,85,100'
INSERT try1 select 'b', '50,17'
INSERT try1 select 'c', '6,2,11'
create table dbo.Words(WordId int not null identity(1,1),
Word nvarchar(250) not null, DefaultLanguageCode varchar(10))
insert into Words(Word)
select word From try1
Create table dbo.Translations(
translationId int not null identity(1,1),
wordId int,
Translation nvarchar(2500),
LanguageCode varchar(10)
);
;with src
as
(
SELECT A.WordId,
Split.a.value('.', 'VARCHAR(100)') AS Translation
FROM
(
SELECT WordId,
CAST ('<M>' + REPLACE(trans, ',', '</M><M>') + '</M>' AS XML) AS Data
FROM try1 a
join Words w on a.word = w.Word
) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
)
insert into Translations(wordId, Translation)
select wordId, Translation
from src
select * from Words
select * From Translations
1 Like
hi
i am also on SQL 2008
please give me 1 day
i will work on this
i dont have acces to my computer
hi elic
i tried this
its not complete
i just took one row and tried.. INSERT try1 select 'a', '10,85,100'
its working
i have to try to get it to work with multiple rows
dont have time now ..
i hope this helps
seniors please help .. thanks
drop create data
use tempdb
go
drop table [try1]
go
CREATE TABLE [dbo].[try1](
[word] [nvarchar](50) NULL,
[trans] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT try1 select 'a', '10,85,100'
GO
select * from try1
go
Sql i tried ... not over yet
; with cte as
(
SELECT
[word],
cast(LEFT([trans], CHARINDEX(',', [trans] + ',') - 1) as varchar) as ok ,
cast(STUFF([trans], 1, CHARINDEX(',', [trans] + ','), '') as varchar) as ok1
from try1
UNION all
SELECT
[word],
cast(LEFT(ok1, CHARINDEX(',', ok1 + ',') - 1) as varchar),
cast(STUFF(ok1, 1, CHARINDEX(',', ok1 + ','), '') as varchar) as ok1
FROM cte
WHERE
ok1 > ''
)
select * from cte
go
Result
drop table try1
CREATE TABLE [dbo].[try1](
[word] nvarchar NULL,
[trans] varchar(max) NULL
) ON [PRIMARY]
GO
INSERT try1 select 'a', '10,85,100'
INSERT try1 select 'b', '50,17'
INSERT try1 select 'c', '6,2,11'
;WITH tmp(word,dataitem,trans) AS
(
SELECT
word,
LEFT(trans, CHARINDEX(',', trans + ',') - 1),
STUFF(trans, 1, CHARINDEX(',', trans + ','), '')
FROM try1
UNION all
SELECT
word,
LEFT(trans, CHARINDEX(',', trans + ',') - 1),
STUFF(trans, 1, CHARINDEX(',', trans + ','), '')
FROM tmp
WHERE
trans > ''
)
SELECT word,dataitem
FROM tmp
ORDER BY word
mannesravya
That's exactly what I tried, I took it from the link of harishgg1 (link)
but it doesn't work.
I tried to add
OPTION (maxrecursion 0)
It gets into an endless loop.
Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "dataitem" of recursive query "tmp".
yosiasz
although I have no clue about the connection between xml and sql, but it works so I am going to use it!
Thanks a lot for the design recommandation + how to apply it
about your question:
currently I translate to only one language
the 2 options are 2 meaning in the same language.
hi elic
mannesravya's
works
I tried it
Gosh folks. I have to tell you that if you're using a string splitter function that uses an rCTE (Recursive CTE), a WHILE loop, XML replacement, or any form of concatenation of delimiters, you're killing performance. Please see the following article for proof of that.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
For those that think "Ok. tl:dr:", here are the results from just one of the tests from that article.
You can see what each color coded line is from the legend in the graphic above except the skinny black line. That's the result of a function called "DelimitedSplit8K' and it blows the other methods away. That's for those using < SQL Server 2012.
For 2012 and above, Eirikur Eiriksson took advantage of the new "LEAD" function that came out in 2012 and added it to the original DelimitedSplit8K code to literally double the speed making it almost as fast as a properly written CLR to do the same thing. You can find his fine rendition of the function at the following link.
http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/
Yes, both are limited to a single delimiter and both are limited to either VARCHAR(8000) or NVARCHAR(4000). If you need to split more than that, then you're probably doing something wrong with your data or simply using the wrong tool.
Most of the other stuff also doesn't return the ordinal position of the individual elements of a split string and the new String_Split() function in 2016 also missed that important functionality.
The bottom line is that everything else makes sucking sounds when it comes to performance (and, frequently, proper functionality) and people need to stop pointing other people to articles and blogs that use the other stuff. They certainly need to stop posting other methods even for supposed "small stuff" because the small stuff becomes a part of a "Death by a Thousand Cuts" that every database seems to suffer because of people making such justifications for "small stuff" that get's used everywhere and a lot.
So, using the original test data from the OP as follows:
CREATE TABLE [dbo].[try1](
[word] [nvarchar](50) NULL,
[trans] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT try1 select 'a', '10,85,100'
INSERT try1 select 'b', '50,17'
INSERT try1 select 'c', '6,2,11'
Doing the split with one of the two renditions (depending on which release of SQL Server you're using, as previously stated) then becomes trivial. Be advised that there's also a DelimitedSplitN4K splitter function in the article at the first link I posted and, because the OP is using NVARCHAR in his example table, we'll use that function instead of DelimitedSplit8K, which was designed to handle VARCHAR() splits.
SELECT t.Word
,split.Item
FROM dbo.try1 t
CROSS APPLY dbo.DelimitedSplitN4K(t.trans,N',') split
;
The result is as follows...
You can either dump that result into a table/temp table using SELECT INTO or join to it.
3 Likes
With a little "tough love" intended ("We're all in this together and I'm pullin' for ya!" --from the Red-Green show), that's how people get into trouble with their code. You "have no clue" and, yet, you're going to use something because "it works" (and you've done no testing for certain special characters, which XML will puke on unless you change the code to use TYPE and that will make it even slower). You'll need to support it in the future and you'll also need to do similar things in the future. You NEED to "get a clue" about such things and the ramifications on performance and resource usage or you could end up damaging your career. Word travels fast in this business and the world isn't as big as you think it is. 
The bottom line is that if you're using the XML method, you're using one of the slowest and most resource intensive methods there is.
3 Likes