Difference between substring in Postgresql

Question

I was checking the PostgreSQL Manual the last days and I have noticed that substring() is explained there with substring('string' FROM [int] FOR [int]).

I normally just use substring('string', [int], [int]) because I learned it that way from MySQL my simple question is now if this two have any differences is the first one maybe the better choice to use in PostgreSQL or does do the same and these are just 2 different kinds of doing the same thing?

You can use substr(string, int, int) instead if you want. I think the substring(... from ...) syntax stems from the SQL standard (somethign which MySQL does not really care about) — user330315
– user330315, Commented Feb 9, 2018 at 11:36
Oke thanks first to both of you so you explained me that substring(...from...) is the normal Syntax while the other one was implemented by mysql makes sense to me even I figured out that mysql is a big difference to all other SQL languages and that's mostly not a good thing! But is there like a speed difference? Or can I just assume that both syntax types just call the same function? — alovaros
– alovaros, Commented Feb 9, 2018 at 12:28

Salman Arshad · Accepted Answer · 2018-02-09 11:46:30Z

3

SUBSTRING(char_exp FROM start_position [FOR length]) syntax is part of SQL-92 standard and you are encouraged to use it.

This syntax also works in MySQL.

edited Feb 9, 2018 at 11:46

answered Feb 9, 2018 at 11:40

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

alovaros Over a year ago

Oke thanks first. I got that substring(.... from ....) is the standard syntax but how does it make a difference or is substring('String',int,int) like an alias?

Salman Arshad Over a year ago

No difference, it is just two syntaxes for same function.

IMSoP Over a year ago

The SQL committee love adding words to things, presumably because they think it makes the syntax more human-readable. My favourite is that they then use the same keywords for other purposes, giving the completely nonsensical syntax SUBSTRING('foo' FROM 'pattern' FOR 'escape-character'), which also takes its own special format of regular expression.

Collectives™ on Stack Overflow

Difference between substring in Postgresql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related