Mixing
Find first instance of space in string
1
I am trying to return the first 'word' in a string by finding the first instance of a space ' ' in the string field Part_Comment. Examples of strings in the field Part_Comment are:
13088V21 () (FAB)
G16707 (FOLD) ()
16636U01.01
I have tried:
substring(Part_Comment from 1 for position(' ' in Part_Comment)-2) as "AssyNo",
which comes up with an error "Incorrect syntax near the keyword 'from'." But it works fine when I just use Part_Comment by itself.
substring(Part_Comment from 1) as "AssyNo",
Same error as above
left(Part_Comment,10) as "AssyNo",
This works, but I need to use the position function or something else to find the ' ' substring. But apparently the position function returns 0 when more than one instance occurs.
I imagine this is a pretty common thing that users want, so there must be an easy solution.
sql-server position substring edited Nov 23 '18 at 9:40
Mark Rotteveel
60.7k1478121
asked Nov 21 '18 at 21:55
Simon KingSimon King
579
|
show 1 more comment
1
I am trying to return the first 'word' in a string by finding the first instance of a space ' ' in the string field Part_Comment. Examples of strings in the field Part_Comment are:
13088V21 () (FAB)
G16707 (FOLD) ()
16636U01.01
I have tried:
substring(Part_Comment from 1 for position(' ' in Part_Comment)-2) as "AssyNo",
which comes up with an error "Incorrect syntax near the keyword 'from'." But it works fine when I just use Part_Comment by itself.
substring(Part_Comment from 1) as "AssyNo",
Same error as above
left(Part_Comment,10) as "AssyNo",
This works, but I need to use the position function or something else to find the ' ' substring. But apparently the position function returns 0 when more than one instance occurs.
I imagine this is a pretty common thing that users want, so there must be an easy solution.
sql-server position substring edited Nov 23 '18 at 9:40
Mark Rotteveel
60.7k1478121
asked Nov 21 '18 at 21:55
Simon KingSimon King
579
1
Are you sure you're using FB2.5? Because support for length (and startpos) being any valid integer expression was added in 2.0, versions below it require that the length must be integer literal.
– ain
Nov 22 '18 at 6:55
2
Are you sure you are using Firebird? "Incorrect syntax near the keyword .." is not a Firebird error message, but a Microsoft SQL Server error message.
– Mark Rotteveel
Nov 22 '18 at 7:46
@MarkRotteveel MS SQL or MySQL? I believe I saw that error often on SQLFiddle with MYSQL 5.6
– Arioch 'The
Nov 22 '18 at 8:39
@Arioch'The MySQL uses "Incorrect syntax near .." (so without 'the keyword')
– Mark Rotteveel
Nov 22 '18 at 8:48
@MarkRotteveel - you are right! It was MS SQL. So I am using the wrong function as position doesn't exist.
– Simon King
Nov 22 '18 at 21:51
|
show 1 more comment
1
1
1
I am trying to return the first 'word' in a string by finding the first instance of a space ' ' in the string field Part_Comment. Examples of strings in the field Part_Comment are:
13088V21 () (FAB)
G16707 (FOLD) ()
16636U01.01
I have tried:
substring(Part_Comment from 1 for position(' ' in Part_Comment)-2) as "AssyNo",
which comes up with an error "Incorrect syntax near the keyword 'from'." But it works fine when I just use Part_Comment by itself.
substring(Part_Comment from 1) as "AssyNo",
Same error as above
left(Part_Comment,10) as "AssyNo",
This works, but I need to use the position function or something else to find the ' ' substring. But apparently the position function returns 0 when more than one instance occurs.
I imagine this is a pretty common thing that users want, so there must be an easy solution.
sql-server position substring edited Nov 23 '18 at 9:40
Mark Rotteveel
60.7k1478121
asked Nov 21 '18 at 21:55
Simon KingSimon King
579
I am trying to return the first 'word' in a string by finding the first instance of a space ' ' in the string field Part_Comment. Examples of strings in the field Part_Comment are:
13088V21 () (FAB)
G16707 (FOLD) ()
16636U01.01
I have tried:
substring(Part_Comment from 1 for position(' ' in Part_Comment)-2) as "AssyNo",
which comes up with an error "Incorrect syntax near the keyword 'from'." But it works fine when I just use Part_Comment by itself.
substring(Part_Comment from 1) as "AssyNo",
Same error as above
left(Part_Comment,10) as "AssyNo",
This works, but I need to use the position function or something else to find the ' ' substring. But apparently the position function returns 0 when more than one instance occurs.
I imagine this is a pretty common thing that users want, so there must be an easy solution.
sql-server position substring sql-server position substring edited Nov 23 '18 at 9:40
Mark Rotteveel
60.7k1478121
asked Nov 21 '18 at 21:55
Simon KingSimon King
579
edited Nov 23 '18 at 9:40
Mark Rotteveel
60.7k1478121
asked Nov 21 '18 at 21:55
Simon KingSimon King
579
edited Nov 23 '18 at 9:40
Mark Rotteveel
60.7k1478121
edited Nov 23 '18 at 9:40
Mark Rotteveel
60.7k1478121
edited Nov 23 '18 at 9:40
Mark Rotteveel
60.7k1478121
60.7k1478121
asked Nov 21 '18 at 21:55
Simon KingSimon King
579
asked Nov 21 '18 at 21:55
Simon KingSimon King
579
asked Nov 21 '18 at 21:55
Simon KingSimon King
579
579
1
Are you sure you're using FB2.5? Because support for length (and startpos) being any valid integer expression was added in 2.0, versions below it require that the length must be integer literal.
– ain
Nov 22 '18 at 6:55
2
Are you sure you are using Firebird? "Incorrect syntax near the keyword .." is not a Firebird error message, but a Microsoft SQL Server error message.
– Mark Rotteveel
Nov 22 '18 at 7:46
@MarkRotteveel MS SQL or MySQL? I believe I saw that error often on SQLFiddle with MYSQL 5.6
– Arioch 'The
Nov 22 '18 at 8:39
@Arioch'The MySQL uses "Incorrect syntax near .." (so without 'the keyword')
– Mark Rotteveel
Nov 22 '18 at 8:48
@MarkRotteveel - you are right! It was MS SQL. So I am using the wrong function as position doesn't exist.
– Simon King
Nov 22 '18 at 21:51
|
show 1 more comment
1
Are you sure you're using FB2.5? Because support for length (and startpos) being any valid integer expression was added in 2.0, versions below it require that the length must be integer literal.
– ain
Nov 22 '18 at 6:55
2
Are you sure you are using Firebird? "Incorrect syntax near the keyword .." is not a Firebird error message, but a Microsoft SQL Server error message.
– Mark Rotteveel
Nov 22 '18 at 7:46
@MarkRotteveel MS SQL or MySQL? I believe I saw that error often on SQLFiddle with MYSQL 5.6
– Arioch 'The
Nov 22 '18 at 8:39
@Arioch'The MySQL uses "Incorrect syntax near .." (so without 'the keyword')
– Mark Rotteveel
Nov 22 '18 at 8:48
@MarkRotteveel - you are right! It was MS SQL. So I am using the wrong function as position doesn't exist.
– Simon King
Nov 22 '18 at 21:51
1
1
Are you sure you're using FB2.5? Because support for length (and startpos) being any valid integer expression was added in 2.0, versions below it require that the length must be integer literal.
– ain
Nov 22 '18 at 6:55
Are you sure you're using FB2.5? Because support for length (and startpos) being any valid integer expression was added in 2.0, versions below it require that the length must be integer literal.
– ain
Nov 22 '18 at 6:55
2
2
Are you sure you are using Firebird? "Incorrect syntax near the keyword .." is not a Firebird error message, but a Microsoft SQL Server error message.
– Mark Rotteveel
Nov 22 '18 at 7:46
Are you sure you are using Firebird? "Incorrect syntax near the keyword .." is not a Firebird error message, but a Microsoft SQL Server error message.
– Mark Rotteveel
Nov 22 '18 at 7:46
@MarkRotteveel MS SQL or MySQL? I believe I saw that error often on SQLFiddle with MYSQL 5.6
– Arioch 'The
Nov 22 '18 at 8:39
@MarkRotteveel MS SQL or MySQL? I believe I saw that error often on SQLFiddle with MYSQL 5.6
– Arioch 'The
Nov 22 '18 at 8:39
@Arioch'The MySQL uses "Incorrect syntax near .." (so without 'the keyword')
– Mark Rotteveel
Nov 22 '18 at 8:48
@Arioch'The MySQL uses "Incorrect syntax near .." (so without 'the keyword')
– Mark Rotteveel
Nov 22 '18 at 8:48
@MarkRotteveel - you are right! It was MS SQL. So I am using the wrong function as position doesn't exist.
– Simon King
Nov 22 '18 at 21:51
@MarkRotteveel - you are right! It was MS SQL. So I am using the wrong function as position doesn't exist.
– Simon King
Nov 22 '18 at 21:51
|
show 1 more comment
1 Answer
1
active
oldest
votes
0
You can do it with LEFT and POSITION like so:
LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1))
EDIT
as @Arioch 'The in comment suggested, safety need to be implemented
CASE POSITION(' ' in Part_Comment)
WHEN 0 THEN 'Part_Comment'
ELSE LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1)
END
answered Nov 22 '18 at 6:51
Mr. NiceMr. Nice
237111
i bet you have to add acoalescefor a case there was no space at all
– Arioch 'The
Nov 22 '18 at 8:40
@Arioch'The I've made EDIT. Is this OK, or could be done another way.
– Mr. Nice
Nov 22 '18 at 11:39
well,COALESCEfunction is much more concise and easier to read = safer to write thanIIFor full blownCASE. For example you just DID make an error by putting Part_Comment into quotes :-D / And also Part_Comment itself might beNULL
– Arioch 'The
Nov 22 '18 at 12:45
LEFT(Part_Comment, COALESCE(POSITION(' ' in Part_Comment)-1), 0))
– Arioch 'The
Nov 22 '18 at 12:45
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
});
}
});
draft saved
draft discarded
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%2f53421013%2ffind-first-instance-of-space-in-string%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
0
You can do it with LEFT and POSITION like so:
LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1))
EDIT
as @Arioch 'The in comment suggested, safety need to be implemented
CASE POSITION(' ' in Part_Comment)
WHEN 0 THEN 'Part_Comment'
ELSE LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1)
END
answered Nov 22 '18 at 6:51
Mr. NiceMr. Nice
237111
i bet you have to add acoalescefor a case there was no space at all
– Arioch 'The
Nov 22 '18 at 8:40
@Arioch'The I've made EDIT. Is this OK, or could be done another way.
– Mr. Nice
Nov 22 '18 at 11:39
well,COALESCEfunction is much more concise and easier to read = safer to write thanIIFor full blownCASE. For example you just DID make an error by putting Part_Comment into quotes :-D / And also Part_Comment itself might beNULL
– Arioch 'The
Nov 22 '18 at 12:45
LEFT(Part_Comment, COALESCE(POSITION(' ' in Part_Comment)-1), 0))
– Arioch 'The
Nov 22 '18 at 12:45
add a comment |
0
You can do it with LEFT and POSITION like so:
LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1))
EDIT
as @Arioch 'The in comment suggested, safety need to be implemented
CASE POSITION(' ' in Part_Comment)
WHEN 0 THEN 'Part_Comment'
ELSE LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1)
END
answered Nov 22 '18 at 6:51
Mr. NiceMr. Nice
237111
i bet you have to add acoalescefor a case there was no space at all
– Arioch 'The
Nov 22 '18 at 8:40
@Arioch'The I've made EDIT. Is this OK, or could be done another way.
– Mr. Nice
Nov 22 '18 at 11:39
well,COALESCEfunction is much more concise and easier to read = safer to write thanIIFor full blownCASE. For example you just DID make an error by putting Part_Comment into quotes :-D / And also Part_Comment itself might beNULL
– Arioch 'The
Nov 22 '18 at 12:45
LEFT(Part_Comment, COALESCE(POSITION(' ' in Part_Comment)-1), 0))
– Arioch 'The
Nov 22 '18 at 12:45
add a comment |
0
0
0
You can do it with LEFT and POSITION like so:
LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1))
EDIT
as @Arioch 'The in comment suggested, safety need to be implemented
CASE POSITION(' ' in Part_Comment)
WHEN 0 THEN 'Part_Comment'
ELSE LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1)
END
answered Nov 22 '18 at 6:51
Mr. NiceMr. Nice
237111
You can do it with LEFT and POSITION like so:
LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1))
EDIT
as @Arioch 'The in comment suggested, safety need to be implemented
CASE POSITION(' ' in Part_Comment)
WHEN 0 THEN 'Part_Comment'
ELSE LEFT(Part_Comment, POSITION(' ' in Part_Comment)-1)
END
answered Nov 22 '18 at 6:51
Mr. NiceMr. Nice
237111
edited Nov 22 '18 at 11:39
answered Nov 22 '18 at 6:51
Mr. NiceMr. Nice
237111
answered Nov 22 '18 at 6:51
Mr. NiceMr. Nice
237111
answered Nov 22 '18 at 6:51
Mr. NiceMr. Nice
237111
237111
i bet you have to add acoalescefor a case there was no space at all
– Arioch 'The
Nov 22 '18 at 8:40
@Arioch'The I've made EDIT. Is this OK, or could be done another way.
– Mr. Nice
Nov 22 '18 at 11:39
well,COALESCEfunction is much more concise and easier to read = safer to write thanIIFor full blownCASE. For example you just DID make an error by putting Part_Comment into quotes :-D / And also Part_Comment itself might beNULL
– Arioch 'The
Nov 22 '18 at 12:45
LEFT(Part_Comment, COALESCE(POSITION(' ' in Part_Comment)-1), 0))
– Arioch 'The
Nov 22 '18 at 12:45
add a comment |
i bet you have to add acoalescefor a case there was no space at all
– Arioch 'The
Nov 22 '18 at 8:40
@Arioch'The I've made EDIT. Is this OK, or could be done another way.
– Mr. Nice
Nov 22 '18 at 11:39
well,COALESCEfunction is much more concise and easier to read = safer to write thanIIFor full blownCASE. For example you just DID make an error by putting Part_Comment into quotes :-D / And also Part_Comment itself might beNULL
– Arioch 'The
Nov 22 '18 at 12:45
LEFT(Part_Comment, COALESCE(POSITION(' ' in Part_Comment)-1), 0))
– Arioch 'The
Nov 22 '18 at 12:45
i bet you have to add a
coalesce for a case there was no space at all– Arioch 'The
Nov 22 '18 at 8:40
i bet you have to add a
coalesce for a case there was no space at all– Arioch 'The
Nov 22 '18 at 8:40
@Arioch'The I've made EDIT. Is this OK, or could be done another way.
– Mr. Nice
Nov 22 '18 at 11:39
@Arioch'The I've made EDIT. Is this OK, or could be done another way.
– Mr. Nice
Nov 22 '18 at 11:39
well,
COALESCE function is much more concise and easier to read = safer to write than IIF or full blown CASE. For example you just DID make an error by putting Part_Comment into quotes :-D / And also Part_Comment itself might be NULL– Arioch 'The
Nov 22 '18 at 12:45
well,
COALESCE function is much more concise and easier to read = safer to write than IIF or full blown CASE. For example you just DID make an error by putting Part_Comment into quotes :-D / And also Part_Comment itself might be NULL– Arioch 'The
Nov 22 '18 at 12:45
LEFT(Part_Comment, COALESCE(POSITION(' ' in Part_Comment)-1), 0))– Arioch 'The
Nov 22 '18 at 12:45
LEFT(Part_Comment, COALESCE(POSITION(' ' in Part_Comment)-1), 0))– Arioch 'The
Nov 22 '18 at 12:45
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53421013%2ffind-first-instance-of-space-in-string%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
1
Are you sure you're using FB2.5? Because support for length (and startpos) being any valid integer expression was added in 2.0, versions below it require that the length must be integer literal.
– ain
Nov 22 '18 at 6:55
2
Are you sure you are using Firebird? "Incorrect syntax near the keyword .." is not a Firebird error message, but a Microsoft SQL Server error message.
– Mark Rotteveel
Nov 22 '18 at 7:46
@MarkRotteveel MS SQL or MySQL? I believe I saw that error often on SQLFiddle with MYSQL 5.6
– Arioch 'The
Nov 22 '18 at 8:39
@Arioch'The MySQL uses "Incorrect syntax near .." (so without 'the keyword')
– Mark Rotteveel
Nov 22 '18 at 8:48
@MarkRotteveel - you are right! It was MS SQL. So I am using the wrong function as position doesn't exist.
– Simon King
Nov 22 '18 at 21:51