Replace columns separated by string with id from another table - SQL Server
I have following 2 tables in SQL Server
Category table:
Category
--------------------------
Delivery;Gauges;Book;Table
Category id:
id name
-----------------
13183 Delivery
88781 Gauges
88782 Book
12512 Table
Intended result is to have category table replaced with category id, as:
Category
-----------------------
13183;88781;88782;12512
I approached this by first separating category columns into separate columns using :
ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
and so on. Then used left join and replace on each new column. Isn't there an easier way to do this? I searched on the net and stackoverflow but can't seem to find anything similar.
sql
sql-server replace edited Nov 14 '18 at 5:50
marc_s
574k12811091256
asked Nov 13 '18 at 23:31
SimranSimran
254
add a comment |
I have following 2 tables in SQL Server
Category table:
Category
--------------------------
Delivery;Gauges;Book;Table
Category id:
id name
-----------------
13183 Delivery
88781 Gauges
88782 Book
12512 Table
Intended result is to have category table replaced with category id, as:
Category
-----------------------
13183;88781;88782;12512
I approached this by first separating category columns into separate columns using :
ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
and so on. Then used left join and replace on each new column. Isn't there an easier way to do this? I searched on the net and stackoverflow but can't seem to find anything similar.
sql
sql-server replace edited Nov 14 '18 at 5:50
marc_s
574k12811091256
asked Nov 13 '18 at 23:31
SimranSimran
254
Possible duplicate of How to make a query with group_concat in sql server
– scsimon
Nov 14 '18 at 1:02
2
Fix your data! Don't store lists in strings!
– Gordon Linoff
Nov 14 '18 at 4:30
add a comment |
I have following 2 tables in SQL Server
Category table:
Category
--------------------------
Delivery;Gauges;Book;Table
Category id:
id name
-----------------
13183 Delivery
88781 Gauges
88782 Book
12512 Table
Intended result is to have category table replaced with category id, as:
Category
-----------------------
13183;88781;88782;12512
I approached this by first separating category columns into separate columns using :
ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
and so on. Then used left join and replace on each new column. Isn't there an easier way to do this? I searched on the net and stackoverflow but can't seem to find anything similar.
sql
sql-server replace edited Nov 14 '18 at 5:50
marc_s
574k12811091256
asked Nov 13 '18 at 23:31
SimranSimran
254
I have following 2 tables in SQL Server
Category table:
Category
--------------------------
Delivery;Gauges;Book;Table
Category id:
id name
-----------------
13183 Delivery
88781 Gauges
88782 Book
12512 Table
Intended result is to have category table replaced with category id, as:
Category
-----------------------
13183;88781;88782;12512
I approached this by first separating category columns into separate columns using :
ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
and so on. Then used left join and replace on each new column. Isn't there an easier way to do this? I searched on the net and stackoverflow but can't seem to find anything similar.
sql
sql-server replace sql
sql-server replace edited Nov 14 '18 at 5:50
marc_s
574k12811091256
asked Nov 13 '18 at 23:31
SimranSimran
254
edited Nov 14 '18 at 5:50
marc_s
574k12811091256
asked Nov 13 '18 at 23:31
SimranSimran
254
edited Nov 14 '18 at 5:50
marc_s
574k12811091256
edited Nov 14 '18 at 5:50
marc_s
574k12811091256
edited Nov 14 '18 at 5:50
marc_s
574k12811091256
574k12811091256
asked Nov 13 '18 at 23:31
SimranSimran
254
asked Nov 13 '18 at 23:31
SimranSimran
254
asked Nov 13 '18 at 23:31
SimranSimran
254
254
Possible duplicate of How to make a query with group_concat in sql server
– scsimon
Nov 14 '18 at 1:02
2
Fix your data! Don't store lists in strings!
– Gordon Linoff
Nov 14 '18 at 4:30
add a comment |
Possible duplicate of How to make a query with group_concat in sql server
– scsimon
Nov 14 '18 at 1:02
2
Fix your data! Don't store lists in strings!
– Gordon Linoff
Nov 14 '18 at 4:30
Possible duplicate of How to make a query with group_concat in sql server
– scsimon
Nov 14 '18 at 1:02
Possible duplicate of How to make a query with group_concat in sql server
– scsimon
Nov 14 '18 at 1:02
2
2
Fix your data! Don't store lists in strings!
– Gordon Linoff
Nov 14 '18 at 4:30
Fix your data! Don't store lists in strings!
– Gordon Linoff
Nov 14 '18 at 4:30
add a comment |
1 Answer
1
active
oldest
votes
You can try to make a function to split your string value by a character.
CREATE FUNCTION Split_fun
( @Words nvarchar(MAX)
, @splitStr varchar(50)
)
RETURNS @Result_Table TABLE
(
[word] nvarchar(max) NULL
)
BEGIN
Declare @TempStr nvarchar(MAX)
WHILE (CHARINDEX(@splitStr,@Words)>0)
BEGIN
Set @TempStr=SUBSTRING(@Words,1,CHARINDEX(@splitStr,@Words)-1)
Insert into @Result_Table (word) Values (@TempStr)
Set @Words = REPLACE(@Words,@TempStr+@splitStr,'')
END/*End While*/
IF(LEN(RTRIM(LTRIM(@Words)))>0 And CHARINDEX(@splitStr,RTRIM(LTRIM(@Words)))=0)
Begin
Set @TempStr=@Words
Insert into @Result_Table (word) Values (@TempStr)
End
RETURN
END
you can use this function to make a result set by
';'.do self-join with
Category idtable.
final you can use FOR XML connect all string by ; to get your expectation result.
;with cte as (
SELECT id
FROM T CROSS APPLY Split_fun(Category,';') v
JOIN T1 on v.word = t1.Category
)
select STUFF((
select distinct ';'+ cast(id as varchar(10))
FROM cte
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
sqlfiddle
answered Nov 14 '18 at 1:49
D-ShihD-Shih
25.8k61531
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53291024%2freplace-columns-separated-by-string-with-id-from-another-table-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can try to make a function to split your string value by a character.
CREATE FUNCTION Split_fun
( @Words nvarchar(MAX)
, @splitStr varchar(50)
)
RETURNS @Result_Table TABLE
(
[word] nvarchar(max) NULL
)
BEGIN
Declare @TempStr nvarchar(MAX)
WHILE (CHARINDEX(@splitStr,@Words)>0)
BEGIN
Set @TempStr=SUBSTRING(@Words,1,CHARINDEX(@splitStr,@Words)-1)
Insert into @Result_Table (word) Values (@TempStr)
Set @Words = REPLACE(@Words,@TempStr+@splitStr,'')
END/*End While*/
IF(LEN(RTRIM(LTRIM(@Words)))>0 And CHARINDEX(@splitStr,RTRIM(LTRIM(@Words)))=0)
Begin
Set @TempStr=@Words
Insert into @Result_Table (word) Values (@TempStr)
End
RETURN
END
you can use this function to make a result set by
';'.do self-join with
Category idtable.
final you can use FOR XML connect all string by ; to get your expectation result.
;with cte as (
SELECT id
FROM T CROSS APPLY Split_fun(Category,';') v
JOIN T1 on v.word = t1.Category
)
select STUFF((
select distinct ';'+ cast(id as varchar(10))
FROM cte
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
sqlfiddle
answered Nov 14 '18 at 1:49
D-ShihD-Shih
25.8k61531
add a comment |
You can try to make a function to split your string value by a character.
CREATE FUNCTION Split_fun
( @Words nvarchar(MAX)
, @splitStr varchar(50)
)
RETURNS @Result_Table TABLE
(
[word] nvarchar(max) NULL
)
BEGIN
Declare @TempStr nvarchar(MAX)
WHILE (CHARINDEX(@splitStr,@Words)>0)
BEGIN
Set @TempStr=SUBSTRING(@Words,1,CHARINDEX(@splitStr,@Words)-1)
Insert into @Result_Table (word) Values (@TempStr)
Set @Words = REPLACE(@Words,@TempStr+@splitStr,'')
END/*End While*/
IF(LEN(RTRIM(LTRIM(@Words)))>0 And CHARINDEX(@splitStr,RTRIM(LTRIM(@Words)))=0)
Begin
Set @TempStr=@Words
Insert into @Result_Table (word) Values (@TempStr)
End
RETURN
END
you can use this function to make a result set by
';'.do self-join with
Category idtable.
final you can use FOR XML connect all string by ; to get your expectation result.
;with cte as (
SELECT id
FROM T CROSS APPLY Split_fun(Category,';') v
JOIN T1 on v.word = t1.Category
)
select STUFF((
select distinct ';'+ cast(id as varchar(10))
FROM cte
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
sqlfiddle
answered Nov 14 '18 at 1:49
D-ShihD-Shih
25.8k61531
add a comment |
You can try to make a function to split your string value by a character.
CREATE FUNCTION Split_fun
( @Words nvarchar(MAX)
, @splitStr varchar(50)
)
RETURNS @Result_Table TABLE
(
[word] nvarchar(max) NULL
)
BEGIN
Declare @TempStr nvarchar(MAX)
WHILE (CHARINDEX(@splitStr,@Words)>0)
BEGIN
Set @TempStr=SUBSTRING(@Words,1,CHARINDEX(@splitStr,@Words)-1)
Insert into @Result_Table (word) Values (@TempStr)
Set @Words = REPLACE(@Words,@TempStr+@splitStr,'')
END/*End While*/
IF(LEN(RTRIM(LTRIM(@Words)))>0 And CHARINDEX(@splitStr,RTRIM(LTRIM(@Words)))=0)
Begin
Set @TempStr=@Words
Insert into @Result_Table (word) Values (@TempStr)
End
RETURN
END
you can use this function to make a result set by
';'.do self-join with
Category idtable.
final you can use FOR XML connect all string by ; to get your expectation result.
;with cte as (
SELECT id
FROM T CROSS APPLY Split_fun(Category,';') v
JOIN T1 on v.word = t1.Category
)
select STUFF((
select distinct ';'+ cast(id as varchar(10))
FROM cte
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
sqlfiddle
answered Nov 14 '18 at 1:49
D-ShihD-Shih
25.8k61531
You can try to make a function to split your string value by a character.
CREATE FUNCTION Split_fun
( @Words nvarchar(MAX)
, @splitStr varchar(50)
)
RETURNS @Result_Table TABLE
(
[word] nvarchar(max) NULL
)
BEGIN
Declare @TempStr nvarchar(MAX)
WHILE (CHARINDEX(@splitStr,@Words)>0)
BEGIN
Set @TempStr=SUBSTRING(@Words,1,CHARINDEX(@splitStr,@Words)-1)
Insert into @Result_Table (word) Values (@TempStr)
Set @Words = REPLACE(@Words,@TempStr+@splitStr,'')
END/*End While*/
IF(LEN(RTRIM(LTRIM(@Words)))>0 And CHARINDEX(@splitStr,RTRIM(LTRIM(@Words)))=0)
Begin
Set @TempStr=@Words
Insert into @Result_Table (word) Values (@TempStr)
End
RETURN
END
you can use this function to make a result set by
';'.do self-join with
Category idtable.
final you can use FOR XML connect all string by ; to get your expectation result.
;with cte as (
SELECT id
FROM T CROSS APPLY Split_fun(Category,';') v
JOIN T1 on v.word = t1.Category
)
select STUFF((
select distinct ';'+ cast(id as varchar(10))
FROM cte
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
sqlfiddle
answered Nov 14 '18 at 1:49
D-ShihD-Shih
25.8k61531
edited Nov 14 '18 at 1:54
answered Nov 14 '18 at 1:49
D-ShihD-Shih
25.8k61531
answered Nov 14 '18 at 1:49
D-ShihD-Shih
25.8k61531
answered Nov 14 '18 at 1:49
D-ShihD-Shih
25.8k61531
25.8k61531
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53291024%2freplace-columns-separated-by-string-with-id-from-another-table-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Possible duplicate of How to make a query with group_concat in sql server
– scsimon
Nov 14 '18 at 1:02
2
Fix your data! Don't store lists in strings!
– Gordon Linoff
Nov 14 '18 at 4:30