Skip to content

Beginner SQL – SQL String Manipulation

SQL commands tend to return the results in a fixed format that looks like a table. However SQL offer other ways to present and combine the data. In this post I will explore how to do this and the ways we can do SQL String Manipulation for MariaDB.

Table of Contents


Database Host

In a previous post I installed MariaDB and set up a database and discussed SQL Command Syntax. So I will use this Database and previously discussed commands in this post. My database is running with the following config.

IP Address192.168.1.163
Password mypassword
Database Nameemployees
Database information

Manipulating Strings in SQL


“substr” extracts part of a string from a string. For example if you needed to get all the characters from the string “The Green Car” you could use the below syntax,

select substr("The Green Car",1);
From the 1st Character

The number “1” here represents the starting point in the String you want to grab the string from. For example f you needed to get everything after the “The ” then you know you should start at the 4th character.

select substr("The Green Car",4);
From the 4th Character

Suppose you just want the last Three Characters? Then its just a matter of using a minus number

select substr("The Green Car",-3);
Last 3 Characters

And if you just want to get a range of Characters, then you add an other number. First number is the starting point and second number is the amount of Characters to get.

select substr("The Green Car",4,6);
Limited Range


Prints the length of a string.

select length("The Green Car");
String Length

It can also be used to get the length of the database name.

select length(database());
Database Name length


Delimiters can be really useful to make data readable and manageable in other apps if its exported. When using a statement with a “group_concat” it can be useful to delimited the data somehow. Lets take a look at the example given for the “group_concat” statement.

In this example there are two select statements joined together by a Union statement. The “group_concat” in the second “select” is grabbing an extra column (hire_date) that should not be allowed as the rules for union statements says the “the same number of columns must be in each select” so this is a fudge to get more data back. Typically seen in a sqli or blind sqli injection attack.

select first_name,last_name from employees where emp_no = '227474' union select first_name,group_concat(first_name,hire_date) from employees where emp_no = '242779';
No Delimiter

We can easily add a delimiter between “first_name,hire_data”. We can add ” || ” between them separated by commas.

select first_name,last_name from employees where emp_no = '227474' union select first_name,group_concat(first_name, " || " , hire_date) from employees where emp_no = '242779';
With a delimiter


When a “union” statement is used in conjunction with a “group_concat“, the data can be hard to read and messy.

No Carriage Returns or Separators.

However the “group_concat” has a separator option can be added between each row. For example you can make it a carriage return with the below code.

select first_name,last_name from employees where emp_no = '10010' union select NULL, group_concat(first_name," || ", last_name," || ",emp_no separator '\n') from employees where first_name = 'barry';
With Carriage Returns and Separators


Although SQL String Manipulation would not normally come high in any SQL course, its is an essential skill to learn if you are interested in doing Capture the Flag challenges.


I really enjoy making this content and if you would like to support the cost of keeping this site up and running, please make a purchase through one of my affiliate links.

Published inCTFLinuxSQL

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *