I want to select a field from table and substring it.
For example:
VAN1031 --> 1031
I tried this, but is improper syntax:
SELECT SUBSTR(R.regnumber,3,3) from registration R
How can this be done?
6 Answers
You don't need the third argument (length) if you want to select all the characters to the right of a specific index:
SELECT SUBSTR(R.regnumber, 4)
FROM registration AS R
I also changed the start index to 4 because in SQL strings are 1-indexed and not 0-indexed as they are in many popular programming languages.
3 Comments
Shafizadeh
I don't know why
substr(col, 4) does not work for me, and I have to use it like this: substr(col, 1, 4). why? My problem is related to the MySQL version?
CommandZ
WHY is this not 0 indexed? You would think this wasn't built by computer scientists
felwithe
@CommandZ WARNING! This is not just a joke! If you use a 0 start index SUBSTR will return an empty string!
You can use:
SUBSTR(string,position)
SUBSTR(string,position,length)
SUBSTRING_INDEX(string, delimiter, count)
Examples:
command prints
------------------------------------------- -----------
select substr("abcd", 1, 1) #a
select substr("abcd", 1, 2) #ab
select substr("abcd", 2, 1) #b
select substr("abcd", 2, 2) #bc
select substr("abcd", -2, 1) #c
select substr("abcd", -2, 2) #cd
select substring_index('ababab', 'b', 1); #a
select substring_index('ababab', 'b', 2); #aba
select substring_index('ababab', 'b', 3); #ababa
select substring_index('ababab', 'b', -1); #
select substring_index('ababab', 'b', -2); #ab
select substring_index('ababab', 'b', -3); #abab
select substr("abcd", 2) #bcd
select substr("abcd", 3) #cd
select substr("abcd", 4) #d
select substr("abcd", -2) #cd
select substr("abcd", -3) #bcd
select substr("abcd", -4) #abcd
From this link.
2 Comments
machineaddict
In your examples
ababab should be replaced with abcdef as the output is confusing because of repeating ab text.
Paul
For future reference, the examples are
ababab because of what they are demonstrating. substring_index returns a substring up to a certain number of occurances of a delimiter. In this case, b is being used as the delimiter. If abcdef was used, the result of those examples would be very different.You can use SUBSTRING():
select substring(col1, 4)
from table1
See SQL Fiddle with Demo. So your query would be:
SELECT substring(R.regnumber,4)
from registration R
Of if you want to specify the number of characters to return:
select substring(col1, 4, 4)
from table1
Comments
I noticed that mysql index starts from 1 instead of zero as many programming languages did.
SELECT SUBSTRING(R.regNumber,1,3) FROM registration AS R
Returns VAN and
SELECT SUBSTRING(R.regNumber,4) FROM registration AS R
Returns the remaining part of the string as 1031
answered May 8, 2017 at 8:49
Ruberandinda Patience
3,8153 gold badges22 silver badges19 bronze badges
Comments
SELECT substring(R.regnumber FROM 4) FROM registration AS R;
and if you want to take the part as an integer not a string you can write:
SELECT CAST(substring(R.regnumber FROM 4) AS UNSIGNED) FROM registration as R;
Comments
Sometimes you need to catch the column without some last characters. For example, I have:
This is a string
Let's say that for some reason I want column without last 6 characters:
This is a
We can do (using @bluefeet 's answer and LENGHT):
select substring(col1, 1,LENGTH(col1)-7)
from table1
It was only an example, but you got the idea, I'm using to fix a wrong database import.
SELECT SUBSTRING('VAN1031', 4, 4)- start at the 4th character and take 4 characters from the string ;-)select "penguins", substr("penguins", 1, 3)prints:penguins pen