How to substring a MySQL table column

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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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.

Method 3

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

Method 4

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

Method 5

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;

Method 6

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating