How do I get part of a string with SQL? (part 2)

Remember when we previously talked about extracting part of a string with SQL?

As SQL Server and PostgreSQL would not support a “mid” function, getting a string that was placed in the middle of another could be a bit tricky….

We then solved it by applying a double right() and left() functions. There is, however, another function that could directly give you the same end result. It is called substring()

As you’ll see below, it’s supported by all three databases and it also takes on the exact same syntax!

-----------------------SQL server-------------------------------
select
'I love you' as phrase,
substring('I love you', 3,4) as phrase2
phrase |phrase2 |
-----------|--------|
I love you |love |
-----------------------PostgreSQL--------------------------------
select
'I love you' as phrase,
substring('I love you', 3,4) as phrase2,
substr('I love you', 3,4) as phrase3
phrase |phrase2 |phrase3 |
-----------|--------|--------|
I love you |love |love |
------------------------MySQL-------------------------------------
select
'I love you' as phrase,
substring('i love you', 3, 4) as phrase2,
substr('I love you.', 3, 4) as phrase3
phrase |phrase2 |phrase3 |
-----------|--------|--------|
I love you |love |love |
view raw SQL_substring.sql hosted with ❤ by GitHub

NB! Note that PostgreSQL and MySQL also support another variant of the same function – substr()

Happy SQL-ing!