Mixing
SQL SELECT where cell is a certain length and includes specific characters
I'm trying to create a SELECT statement that selects rows where NAME is max. 5 characters and the . is in the NAME.
I only want the first, so I'm including a LIMIT 1 to the statement.
I have worked with the following
searchstring = "."
sql = "SELECT * FROM Table WHERE NAME LIKE %s LIMIT 1"
val = (("%"+searchstring+"%"),)
cursor.execute(sql, val)
But I'm not sure how to incorporate the length of NAME in my statement.
My "Table" is as follows:
ID NAME
1 Jim
2 J.
3 Jonathan
4 Jack M.
5 M.S.
So based on the table above, I would expect row 2 and 5 to be selected.
I could select all, and loop through them. But as I only want the first, I'm thinking I would prefer a SQL statement?
Thanks in advance.
python sql mariadb
asked Jan 1 at 14:03
letsdothisletsdothis
145
add a comment |
I'm trying to create a SELECT statement that selects rows where NAME is max. 5 characters and the . is in the NAME.
I only want the first, so I'm including a LIMIT 1 to the statement.
I have worked with the following
searchstring = "."
sql = "SELECT * FROM Table WHERE NAME LIKE %s LIMIT 1"
val = (("%"+searchstring+"%"),)
cursor.execute(sql, val)
But I'm not sure how to incorporate the length of NAME in my statement.
My "Table" is as follows:
ID NAME
1 Jim
2 J.
3 Jonathan
4 Jack M.
5 M.S.
So based on the table above, I would expect row 2 and 5 to be selected.
I could select all, and loop through them. But as I only want the first, I'm thinking I would prefer a SQL statement?
Thanks in advance.
python sql mariadb
asked Jan 1 at 14:03
letsdothisletsdothis
145
You want lenght of name? and based on that you want to pick first two results? am i right?
– Amit Gandole
Jan 1 at 14:06
I want to check if the length of the NAME is max 5 characters, and I want to select the first row that fulfills both.
– letsdothis
Jan 1 at 14:08
Have you tried using LEN() function? Be sure to delete trailing spaces.
– Amit Gandole
Jan 1 at 14:11
add a comment |
I'm trying to create a SELECT statement that selects rows where NAME is max. 5 characters and the . is in the NAME.
I only want the first, so I'm including a LIMIT 1 to the statement.
I have worked with the following
searchstring = "."
sql = "SELECT * FROM Table WHERE NAME LIKE %s LIMIT 1"
val = (("%"+searchstring+"%"),)
cursor.execute(sql, val)
But I'm not sure how to incorporate the length of NAME in my statement.
My "Table" is as follows:
ID NAME
1 Jim
2 J.
3 Jonathan
4 Jack M.
5 M.S.
So based on the table above, I would expect row 2 and 5 to be selected.
I could select all, and loop through them. But as I only want the first, I'm thinking I would prefer a SQL statement?
Thanks in advance.
python sql mariadb
asked Jan 1 at 14:03
letsdothisletsdothis
145
I'm trying to create a SELECT statement that selects rows where NAME is max. 5 characters and the . is in the NAME.
I only want the first, so I'm including a LIMIT 1 to the statement.
I have worked with the following
searchstring = "."
sql = "SELECT * FROM Table WHERE NAME LIKE %s LIMIT 1"
val = (("%"+searchstring+"%"),)
cursor.execute(sql, val)
But I'm not sure how to incorporate the length of NAME in my statement.
My "Table" is as follows:
ID NAME
1 Jim
2 J.
3 Jonathan
4 Jack M.
5 M.S.
So based on the table above, I would expect row 2 and 5 to be selected.
I could select all, and loop through them. But as I only want the first, I'm thinking I would prefer a SQL statement?
Thanks in advance.
python sql mariadb
python sql mariadb
asked Jan 1 at 14:03
letsdothisletsdothis
145
asked Jan 1 at 14:03
letsdothisletsdothis
145
asked Jan 1 at 14:03
letsdothisletsdothis
145
asked Jan 1 at 14:03
letsdothisletsdothis
145
asked Jan 1 at 14:03
letsdothisletsdothis
145
145
You want lenght of name? and based on that you want to pick first two results? am i right?
– Amit Gandole
Jan 1 at 14:06
I want to check if the length of the NAME is max 5 characters, and I want to select the first row that fulfills both.
– letsdothis
Jan 1 at 14:08
Have you tried using LEN() function? Be sure to delete trailing spaces.
– Amit Gandole
Jan 1 at 14:11
add a comment |
You want lenght of name? and based on that you want to pick first two results? am i right?
– Amit Gandole
Jan 1 at 14:06
I want to check if the length of the NAME is max 5 characters, and I want to select the first row that fulfills both.
– letsdothis
Jan 1 at 14:08
Have you tried using LEN() function? Be sure to delete trailing spaces.
– Amit Gandole
Jan 1 at 14:11
You want lenght of name? and based on that you want to pick first two results? am i right?
– Amit Gandole
Jan 1 at 14:06
You want lenght of name? and based on that you want to pick first two results? am i right?
– Amit Gandole
Jan 1 at 14:06
I want to check if the length of the NAME is max 5 characters, and I want to select the first row that fulfills both.
– letsdothis
Jan 1 at 14:08
I want to check if the length of the NAME is max 5 characters, and I want to select the first row that fulfills both.
– letsdothis
Jan 1 at 14:08
Have you tried using LEN() function? Be sure to delete trailing spaces.
– Amit Gandole
Jan 1 at 14:11
Have you tried using LEN() function? Be sure to delete trailing spaces.
– Amit Gandole
Jan 1 at 14:11
add a comment |
3 Answers
3
active
oldest
votes
You can use CHAR_LENGTH function along with LIKE:
SELECT * FROM Table WHERE name LIKE '%.%' AND CHAR_LENGTH(name) <= 5 LIMIT 1
answered Jan 1 at 14:17
Salman ASalman A
183k66340438
add a comment |
Try LEN()
Select LEN(result string);
This will return the length of string. but this will count spaces also. Try removing it with LTRIM().
answered Jan 1 at 14:14
Amit GandoleAmit Gandole
707
I want to add it to the SQL statement. sql = "SELECT * FROM Table WHERE NAME LIKE %s and LTRIM(LEN(NAME)) <= 5 LIMIT 1" <- doesn't work?
– letsdothis
Jan 1 at 14:17
1
Can you please try LEN(LTRIM()) ?
– Amit Gandole
Jan 1 at 14:21
add a comment |
Oracle SQL
SELECT * FROM Table WHERE name LIKE '%.%' AND LENGTH(name) < 6 and rownum < 2
Base on the sql language(oracle, mysql, sql server, etc) use
length() or char_length()
rownum or limit
answered Jan 1 at 15:08
NimNim
12
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%2f53996095%2fsql-select-where-cell-is-a-certain-length-and-includes-specific-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use CHAR_LENGTH function along with LIKE:
SELECT * FROM Table WHERE name LIKE '%.%' AND CHAR_LENGTH(name) <= 5 LIMIT 1
answered Jan 1 at 14:17
Salman ASalman A
183k66340438
add a comment |
You can use CHAR_LENGTH function along with LIKE:
SELECT * FROM Table WHERE name LIKE '%.%' AND CHAR_LENGTH(name) <= 5 LIMIT 1
answered Jan 1 at 14:17
Salman ASalman A
183k66340438
add a comment |
You can use CHAR_LENGTH function along with LIKE:
SELECT * FROM Table WHERE name LIKE '%.%' AND CHAR_LENGTH(name) <= 5 LIMIT 1
answered Jan 1 at 14:17
Salman ASalman A
183k66340438
You can use CHAR_LENGTH function along with LIKE:
SELECT * FROM Table WHERE name LIKE '%.%' AND CHAR_LENGTH(name) <= 5 LIMIT 1
answered Jan 1 at 14:17
Salman ASalman A
183k66340438
answered Jan 1 at 14:17
Salman ASalman A
183k66340438
answered Jan 1 at 14:17
Salman ASalman A
183k66340438
answered Jan 1 at 14:17
Salman ASalman A
183k66340438
183k66340438
add a comment |
add a comment |
Try LEN()
Select LEN(result string);
This will return the length of string. but this will count spaces also. Try removing it with LTRIM().
answered Jan 1 at 14:14
Amit GandoleAmit Gandole
707
I want to add it to the SQL statement. sql = "SELECT * FROM Table WHERE NAME LIKE %s and LTRIM(LEN(NAME)) <= 5 LIMIT 1" <- doesn't work?
– letsdothis
Jan 1 at 14:17
1
Can you please try LEN(LTRIM()) ?
– Amit Gandole
Jan 1 at 14:21
add a comment |
Try LEN()
Select LEN(result string);
This will return the length of string. but this will count spaces also. Try removing it with LTRIM().
answered Jan 1 at 14:14
Amit GandoleAmit Gandole
707
I want to add it to the SQL statement. sql = "SELECT * FROM Table WHERE NAME LIKE %s and LTRIM(LEN(NAME)) <= 5 LIMIT 1" <- doesn't work?
– letsdothis
Jan 1 at 14:17
1
Can you please try LEN(LTRIM()) ?
– Amit Gandole
Jan 1 at 14:21
add a comment |
Try LEN()
Select LEN(result string);
This will return the length of string. but this will count spaces also. Try removing it with LTRIM().
answered Jan 1 at 14:14
Amit GandoleAmit Gandole
707
Try LEN()
Select LEN(result string);
This will return the length of string. but this will count spaces also. Try removing it with LTRIM().
answered Jan 1 at 14:14
Amit GandoleAmit Gandole
707
answered Jan 1 at 14:14
Amit GandoleAmit Gandole
707
answered Jan 1 at 14:14
Amit GandoleAmit Gandole
707
answered Jan 1 at 14:14
Amit GandoleAmit Gandole
707
707
I want to add it to the SQL statement. sql = "SELECT * FROM Table WHERE NAME LIKE %s and LTRIM(LEN(NAME)) <= 5 LIMIT 1" <- doesn't work?
– letsdothis
Jan 1 at 14:17
1
Can you please try LEN(LTRIM()) ?
– Amit Gandole
Jan 1 at 14:21
add a comment |
I want to add it to the SQL statement. sql = "SELECT * FROM Table WHERE NAME LIKE %s and LTRIM(LEN(NAME)) <= 5 LIMIT 1" <- doesn't work?
– letsdothis
Jan 1 at 14:17
1
Can you please try LEN(LTRIM()) ?
– Amit Gandole
Jan 1 at 14:21
I want to add it to the SQL statement. sql = "SELECT * FROM Table WHERE NAME LIKE %s and LTRIM(LEN(NAME)) <= 5 LIMIT 1" <- doesn't work?
– letsdothis
Jan 1 at 14:17
I want to add it to the SQL statement. sql = "SELECT * FROM Table WHERE NAME LIKE %s and LTRIM(LEN(NAME)) <= 5 LIMIT 1" <- doesn't work?
– letsdothis
Jan 1 at 14:17
1
1
Can you please try LEN(LTRIM()) ?
– Amit Gandole
Jan 1 at 14:21
Can you please try LEN(LTRIM()) ?
– Amit Gandole
Jan 1 at 14:21
add a comment |
Oracle SQL
SELECT * FROM Table WHERE name LIKE '%.%' AND LENGTH(name) < 6 and rownum < 2
Base on the sql language(oracle, mysql, sql server, etc) use
length() or char_length()
rownum or limit
answered Jan 1 at 15:08
NimNim
12
add a comment |
Oracle SQL
SELECT * FROM Table WHERE name LIKE '%.%' AND LENGTH(name) < 6 and rownum < 2
Base on the sql language(oracle, mysql, sql server, etc) use
length() or char_length()
rownum or limit
answered Jan 1 at 15:08
NimNim
12
add a comment |
Oracle SQL
SELECT * FROM Table WHERE name LIKE '%.%' AND LENGTH(name) < 6 and rownum < 2
Base on the sql language(oracle, mysql, sql server, etc) use
length() or char_length()
rownum or limit
answered Jan 1 at 15:08
NimNim
12
Oracle SQL
SELECT * FROM Table WHERE name LIKE '%.%' AND LENGTH(name) < 6 and rownum < 2
Base on the sql language(oracle, mysql, sql server, etc) use
length() or char_length()
rownum or limit
answered Jan 1 at 15:08
NimNim
12
answered Jan 1 at 15:08
NimNim
12
answered Jan 1 at 15:08
NimNim
12
answered Jan 1 at 15:08
NimNim
12
12
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%2f53996095%2fsql-select-where-cell-is-a-certain-length-and-includes-specific-characters%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
You want lenght of name? and based on that you want to pick first two results? am i right?
– Amit Gandole
Jan 1 at 14:06
I want to check if the length of the NAME is max 5 characters, and I want to select the first row that fulfills both.
– letsdothis
Jan 1 at 14:08
Have you tried using LEN() function? Be sure to delete trailing spaces.
– Amit Gandole
Jan 1 at 14:11