In mysql, substring query not working properly if strings are repeating
up vote
1
down vote
favorite
i want to replace last 5 digits of a phone number.
i wrote this query :
update users
set
mobile = Replace(mobile, SUBSTRING(mobile, 6, 5), 'xxxxx')
where
email="xxxxx@gmail.com"
It is working fine for numbers like 8100343397
But for numbers like 9090909090 it is replacing from the 2nd digit instead of 6th digit.
I want to know the reason why.
I know it will work with left and concat.
Please just tell me why the above is not working if strings are repeating.
sql mysqli
edited Nov 19 at 12:04
Rajesh Pandya
1,2912819
asked Nov 19 at 11:40
Suborno Samanta
63
add a comment |
up vote
1
down vote
favorite
i want to replace last 5 digits of a phone number.
i wrote this query :
update users
set
mobile = Replace(mobile, SUBSTRING(mobile, 6, 5), 'xxxxx')
where
email="xxxxx@gmail.com"
It is working fine for numbers like 8100343397
But for numbers like 9090909090 it is replacing from the 2nd digit instead of 6th digit.
I want to know the reason why.
I know it will work with left and concat.
Please just tell me why the above is not working if strings are repeating.
sql mysqli
edited Nov 19 at 12:04
Rajesh Pandya
1,2912819
asked Nov 19 at 11:40
Suborno Samanta
63
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
i want to replace last 5 digits of a phone number.
i wrote this query :
update users
set
mobile = Replace(mobile, SUBSTRING(mobile, 6, 5), 'xxxxx')
where
email="xxxxx@gmail.com"
It is working fine for numbers like 8100343397
But for numbers like 9090909090 it is replacing from the 2nd digit instead of 6th digit.
I want to know the reason why.
I know it will work with left and concat.
Please just tell me why the above is not working if strings are repeating.
sql mysqli
edited Nov 19 at 12:04
Rajesh Pandya
1,2912819
asked Nov 19 at 11:40
Suborno Samanta
63
i want to replace last 5 digits of a phone number.
i wrote this query :
update users
set
mobile = Replace(mobile, SUBSTRING(mobile, 6, 5), 'xxxxx')
where
email="xxxxx@gmail.com"
It is working fine for numbers like 8100343397
But for numbers like 9090909090 it is replacing from the 2nd digit instead of 6th digit.
I want to know the reason why.
I know it will work with left and concat.
Please just tell me why the above is not working if strings are repeating.
sql mysqli
sql mysqli
edited Nov 19 at 12:04
Rajesh Pandya
1,2912819
asked Nov 19 at 11:40
Suborno Samanta
63
edited Nov 19 at 12:04
Rajesh Pandya
1,2912819
asked Nov 19 at 11:40
Suborno Samanta
63
edited Nov 19 at 12:04
Rajesh Pandya
1,2912819
edited Nov 19 at 12:04
Rajesh Pandya
1,2912819
edited Nov 19 at 12:04
Rajesh Pandya
1,2912819
1,2912819
asked Nov 19 at 11:40
Suborno Samanta
63
asked Nov 19 at 11:40
Suborno Samanta
63
asked Nov 19 at 11:40
Suborno Samanta
63
63
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
Why are you using replace()?
update users
set mobile = concat(left(model, length(mobile) - 5), 'xxxxx')
where email = 'xxxxx@gmail.com';
Just take all but the last five characters and then append the new values that you want.
answered Nov 19 at 11:42
Gordon Linoff
750k34286393
2
Gordon is correct, but to explain what you are seeing - you are callingREPLACEand passing in a string of characters that it will look for. Despite you passing in the last 5 digits (09090) of the phone number, since it is a repeat of the first 5 characters (starting at the second digit:09090) it will replace that first occurrence instead of the last.
– gmiley
Nov 19 at 11:45
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
Why are you using replace()?
update users
set mobile = concat(left(model, length(mobile) - 5), 'xxxxx')
where email = 'xxxxx@gmail.com';
Just take all but the last five characters and then append the new values that you want.
answered Nov 19 at 11:42
Gordon Linoff
750k34286393
2
Gordon is correct, but to explain what you are seeing - you are callingREPLACEand passing in a string of characters that it will look for. Despite you passing in the last 5 digits (09090) of the phone number, since it is a repeat of the first 5 characters (starting at the second digit:09090) it will replace that first occurrence instead of the last.
– gmiley
Nov 19 at 11:45
add a comment |
up vote
2
down vote
Why are you using replace()?
update users
set mobile = concat(left(model, length(mobile) - 5), 'xxxxx')
where email = 'xxxxx@gmail.com';
Just take all but the last five characters and then append the new values that you want.
answered Nov 19 at 11:42
Gordon Linoff
750k34286393
2
Gordon is correct, but to explain what you are seeing - you are callingREPLACEand passing in a string of characters that it will look for. Despite you passing in the last 5 digits (09090) of the phone number, since it is a repeat of the first 5 characters (starting at the second digit:09090) it will replace that first occurrence instead of the last.
– gmiley
Nov 19 at 11:45
add a comment |
up vote
2
down vote
up vote
2
down vote
Why are you using replace()?
update users
set mobile = concat(left(model, length(mobile) - 5), 'xxxxx')
where email = 'xxxxx@gmail.com';
Just take all but the last five characters and then append the new values that you want.
answered Nov 19 at 11:42
Gordon Linoff
750k34286393
Why are you using replace()?
update users
set mobile = concat(left(model, length(mobile) - 5), 'xxxxx')
where email = 'xxxxx@gmail.com';
Just take all but the last five characters and then append the new values that you want.
answered Nov 19 at 11:42
Gordon Linoff
750k34286393
answered Nov 19 at 11:42
Gordon Linoff
750k34286393
answered Nov 19 at 11:42
Gordon Linoff
750k34286393
answered Nov 19 at 11:42
Gordon Linoff
750k34286393
750k34286393
2
Gordon is correct, but to explain what you are seeing - you are callingREPLACEand passing in a string of characters that it will look for. Despite you passing in the last 5 digits (09090) of the phone number, since it is a repeat of the first 5 characters (starting at the second digit:09090) it will replace that first occurrence instead of the last.
– gmiley
Nov 19 at 11:45
add a comment |
2
Gordon is correct, but to explain what you are seeing - you are callingREPLACEand passing in a string of characters that it will look for. Despite you passing in the last 5 digits (09090) of the phone number, since it is a repeat of the first 5 characters (starting at the second digit:09090) it will replace that first occurrence instead of the last.
– gmiley
Nov 19 at 11:45
2
2
Gordon is correct, but to explain what you are seeing - you are calling
REPLACE and passing in a string of characters that it will look for. Despite you passing in the last 5 digits (09090) of the phone number, since it is a repeat of the first 5 characters (starting at the second digit: 09090) it will replace that first occurrence instead of the last.– gmiley
Nov 19 at 11:45
Gordon is correct, but to explain what you are seeing - you are calling
REPLACE and passing in a string of characters that it will look for. Despite you passing in the last 5 digits (09090) of the phone number, since it is a repeat of the first 5 characters (starting at the second digit: 09090) it will replace that first occurrence instead of the last.– gmiley
Nov 19 at 11:45
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53373867%2fin-mysql-substring-query-not-working-properly-if-strings-are-repeating%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
