Applying a Function to One Row in a Column
I'm trying to apply a splitting function I wrote to one specific row in a table. Later, I'd like to apply the function to the entire column but I'm having trouble with the first step.
I've basically tried every variation below.
SalesPersons is the column I want to apply the split function to.
SELECT ID,
(SELECT ITEM
FROM [dbo].[Split](SalesPersons, ','))
FROM [dbo].[Menu]
WHERE ID = '1234'
AND APPLICANT = 'JohnSmith'
SELECT * dbo.Split(SalesPersons, ',')
FROM [dbo].[Menu]
WHERE ID = '1234'
AND APPLICANT = 'JohnSmith'
I keep getting the following result:
Cannot find either column dbo or the user-defined function or aggregate dbo.Split, or the name is ambiguous.
If anyone could provide any feedback or help, I'd appreciate it so much!
sql-server function split edited Dec 29 '18 at 18:19
Martin Smith
343k58577686
asked Dec 29 '18 at 18:01
Antonia CAntonia C
93
add a comment |
I'm trying to apply a splitting function I wrote to one specific row in a table. Later, I'd like to apply the function to the entire column but I'm having trouble with the first step.
I've basically tried every variation below.
SalesPersons is the column I want to apply the split function to.
SELECT ID,
(SELECT ITEM
FROM [dbo].[Split](SalesPersons, ','))
FROM [dbo].[Menu]
WHERE ID = '1234'
AND APPLICANT = 'JohnSmith'
SELECT * dbo.Split(SalesPersons, ',')
FROM [dbo].[Menu]
WHERE ID = '1234'
AND APPLICANT = 'JohnSmith'
I keep getting the following result:
Cannot find either column dbo or the user-defined function or aggregate dbo.Split, or the name is ambiguous.
If anyone could provide any feedback or help, I'd appreciate it so much!
sql-server function split edited Dec 29 '18 at 18:19
Martin Smith
343k58577686
asked Dec 29 '18 at 18:01
Antonia CAntonia C
93
add a comment |
I'm trying to apply a splitting function I wrote to one specific row in a table. Later, I'd like to apply the function to the entire column but I'm having trouble with the first step.
I've basically tried every variation below.
SalesPersons is the column I want to apply the split function to.
SELECT ID,
(SELECT ITEM
FROM [dbo].[Split](SalesPersons, ','))
FROM [dbo].[Menu]
WHERE ID = '1234'
AND APPLICANT = 'JohnSmith'
SELECT * dbo.Split(SalesPersons, ',')
FROM [dbo].[Menu]
WHERE ID = '1234'
AND APPLICANT = 'JohnSmith'
I keep getting the following result:
Cannot find either column dbo or the user-defined function or aggregate dbo.Split, or the name is ambiguous.
If anyone could provide any feedback or help, I'd appreciate it so much!
sql-server function split edited Dec 29 '18 at 18:19
Martin Smith
343k58577686
asked Dec 29 '18 at 18:01
Antonia CAntonia C
93
I'm trying to apply a splitting function I wrote to one specific row in a table. Later, I'd like to apply the function to the entire column but I'm having trouble with the first step.
I've basically tried every variation below.
SalesPersons is the column I want to apply the split function to.
SELECT ID,
(SELECT ITEM
FROM [dbo].[Split](SalesPersons, ','))
FROM [dbo].[Menu]
WHERE ID = '1234'
AND APPLICANT = 'JohnSmith'
SELECT * dbo.Split(SalesPersons, ',')
FROM [dbo].[Menu]
WHERE ID = '1234'
AND APPLICANT = 'JohnSmith'
I keep getting the following result:
Cannot find either column dbo or the user-defined function or aggregate dbo.Split, or the name is ambiguous.
If anyone could provide any feedback or help, I'd appreciate it so much!
sql-server function split sql-server function split edited Dec 29 '18 at 18:19
Martin Smith
343k58577686
asked Dec 29 '18 at 18:01
Antonia CAntonia C
93
edited Dec 29 '18 at 18:19
Martin Smith
343k58577686
asked Dec 29 '18 at 18:01
Antonia CAntonia C
93
edited Dec 29 '18 at 18:19
Martin Smith
343k58577686
edited Dec 29 '18 at 18:19
Martin Smith
343k58577686
edited Dec 29 '18 at 18:19
Martin Smith
343k58577686
343k58577686
asked Dec 29 '18 at 18:01
Antonia CAntonia C
93
asked Dec 29 '18 at 18:01
Antonia CAntonia C
93
asked Dec 29 '18 at 18:01
Antonia CAntonia C
93
93
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I'm assuming your split function is a Table-Valued Function
Then you would need a CROSS APPLY (use OUTER APPLY to see null values)
Example
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply dbo.Split(SalesPersons, ',') B
Where ID = '1234'
and APPLICANT = 'JohnSmith'
EDIT - If you want the split values in one row
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(100)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(100)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(100)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(100)')))
From (Select Cast('<x>' + replace((Select replace(A.SalesPersons,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A
) B
answered Dec 29 '18 at 18:13
John CappellettiJohn Cappelletti
45.5k62546
This worked! Thank you so much. :)
– Antonia C
Dec 29 '18 at 18:37
@AntoniaC Happy to help
– John Cappelletti
Dec 29 '18 at 19:02
@AntoniaC See EDIT if you want them split in one row
– John Cappelletti
Dec 29 '18 at 19:08
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%2f53972043%2fapplying-a-function-to-one-row-in-a-column%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
I'm assuming your split function is a Table-Valued Function
Then you would need a CROSS APPLY (use OUTER APPLY to see null values)
Example
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply dbo.Split(SalesPersons, ',') B
Where ID = '1234'
and APPLICANT = 'JohnSmith'
EDIT - If you want the split values in one row
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(100)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(100)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(100)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(100)')))
From (Select Cast('<x>' + replace((Select replace(A.SalesPersons,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A
) B
answered Dec 29 '18 at 18:13
John CappellettiJohn Cappelletti
45.5k62546
This worked! Thank you so much. :)
– Antonia C
Dec 29 '18 at 18:37
@AntoniaC Happy to help
– John Cappelletti
Dec 29 '18 at 19:02
@AntoniaC See EDIT if you want them split in one row
– John Cappelletti
Dec 29 '18 at 19:08
add a comment |
I'm assuming your split function is a Table-Valued Function
Then you would need a CROSS APPLY (use OUTER APPLY to see null values)
Example
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply dbo.Split(SalesPersons, ',') B
Where ID = '1234'
and APPLICANT = 'JohnSmith'
EDIT - If you want the split values in one row
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(100)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(100)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(100)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(100)')))
From (Select Cast('<x>' + replace((Select replace(A.SalesPersons,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A
) B
answered Dec 29 '18 at 18:13
John CappellettiJohn Cappelletti
45.5k62546
This worked! Thank you so much. :)
– Antonia C
Dec 29 '18 at 18:37
@AntoniaC Happy to help
– John Cappelletti
Dec 29 '18 at 19:02
@AntoniaC See EDIT if you want them split in one row
– John Cappelletti
Dec 29 '18 at 19:08
add a comment |
I'm assuming your split function is a Table-Valued Function
Then you would need a CROSS APPLY (use OUTER APPLY to see null values)
Example
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply dbo.Split(SalesPersons, ',') B
Where ID = '1234'
and APPLICANT = 'JohnSmith'
EDIT - If you want the split values in one row
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(100)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(100)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(100)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(100)')))
From (Select Cast('<x>' + replace((Select replace(A.SalesPersons,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A
) B
answered Dec 29 '18 at 18:13
John CappellettiJohn Cappelletti
45.5k62546
I'm assuming your split function is a Table-Valued Function
Then you would need a CROSS APPLY (use OUTER APPLY to see null values)
Example
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply dbo.Split(SalesPersons, ',') B
Where ID = '1234'
and APPLICANT = 'JohnSmith'
EDIT - If you want the split values in one row
Select A.*
,B.*
From [dbo].[Menu] A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(100)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(100)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(100)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(100)')))
From (Select Cast('<x>' + replace((Select replace(A.SalesPersons,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A
) B
answered Dec 29 '18 at 18:13
John CappellettiJohn Cappelletti
45.5k62546
edited Dec 29 '18 at 19:35
answered Dec 29 '18 at 18:13
John CappellettiJohn Cappelletti
45.5k62546
answered Dec 29 '18 at 18:13
John CappellettiJohn Cappelletti
45.5k62546
answered Dec 29 '18 at 18:13
John CappellettiJohn Cappelletti
45.5k62546
45.5k62546
This worked! Thank you so much. :)
– Antonia C
Dec 29 '18 at 18:37
@AntoniaC Happy to help
– John Cappelletti
Dec 29 '18 at 19:02
@AntoniaC See EDIT if you want them split in one row
– John Cappelletti
Dec 29 '18 at 19:08
add a comment |
This worked! Thank you so much. :)
– Antonia C
Dec 29 '18 at 18:37
@AntoniaC Happy to help
– John Cappelletti
Dec 29 '18 at 19:02
@AntoniaC See EDIT if you want them split in one row
– John Cappelletti
Dec 29 '18 at 19:08
This worked! Thank you so much. :)
– Antonia C
Dec 29 '18 at 18:37
This worked! Thank you so much. :)
– Antonia C
Dec 29 '18 at 18:37
@AntoniaC Happy to help
– John Cappelletti
Dec 29 '18 at 19:02
@AntoniaC Happy to help
– John Cappelletti
Dec 29 '18 at 19:02
@AntoniaC See EDIT if you want them split in one row
– John Cappelletti
Dec 29 '18 at 19:08
@AntoniaC See EDIT if you want them split in one row
– John Cappelletti
Dec 29 '18 at 19:08
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%2f53972043%2fapplying-a-function-to-one-row-in-a-column%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
