It was pretty cool that the substring() function had the exact same syntax in all SQL Server, MySQL and PostgreSQL
Are there more functions like that?
Check out the ones below!
- replace() = replaces one part of a string with another
- ltrim() = removes leading spaces from a string
- rtrim() = removes trailing spaces from a string
have all absolutely identical syntax in all SQL Server, PostgreSQL, MySQL and Vertica. The last three also support trim(), which removes both trailing and leading spaces from a string
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-----------------------------------------------Vertica------------------------------------------------------------- | |
select | |
replace('We love you', 'We', 'I'), -- reolaces a string with another | |
' I love you ' as MyPhrase, | |
ltrim(' I love you '), --removes spaces on the left | |
rtrim(' I love you '), --removes spaces on the right | |
trim(' I love you ') --removes spaces on both sides | |
------------------------------------------------MySQL--------------------------------------------------------------- | |
select | |
replace('I love you', 'I', 'We') as replace_, | |
length('I love you ') as before_rtrim, | |
rtrim('I love you ') as rtrim_, | |
length(rtrim('I love you ')) as after_rtrim, | |
length(' I love you') as before_ltrim, | |
ltrim(' I love you') as ltrim_, | |
length(ltrim(' I love you')) as after_ltrim, | |
trim(' I love you ') as trim_, | |
length(trim(' I love you ')) as after_trim | |
replace_ |before_rtrim |rtrim_ |after_rtrim |before_ltrim |ltrim_ |after_ltrim |trim_ |after_trim | | |
------------|-------------|-----------|------------|-------------|-----------|------------|-----------|-----------| | |
We love you |11 |I love you |10 |11 |I love you |10 |I love you |10 | | |
------------------------------------------------PostgreSQL--------------------------------------------------------------- | |
select | |
replace('I love you', 'I', 'We'), | |
length('I love you ') as before_rtrim, | |
rtrim('I love you ') as rtrim_, | |
length(rtrim('I love you ')) as after_rtrim, | |
length(' I love you') as before_ltrim, | |
ltrim(' I love you') as ltrim_, | |
length(ltrim(' I love you')) as after_ltrim, | |
trim(' I love you ') as trim_, | |
length(trim(' I love you ')) as after_trim | |
replace |before_rtrim |rtrim_ |after_rtrim |before_ltrim |ltrim_ |after_ltrim |trim_ |after_trim | | |
------------|-------------|-----------|------------|-------------|-----------|------------|-----------|-----------| | |
We love you |11 |I love you |10 |11 |I love you |10 |I love you |10 | | |
---------------------------------------------SQL Server-------------------------------------------------------------------- | |
select | |
replace('I love you', 'I', 'We') as replace_, | |
datalength('I love you ') as before_rtrim, | |
rtrim('I love you ') as rtrim_, | |
datalength(rtrim('I love you ')) as after_rtrim, | |
datalength(' I love you') as before_ltrim, | |
ltrim(' I love you') as ltrim_, | |
datalength(ltrim(' I love you')) as after_ltrim | |
Wanna check out how these come together in a real-life example?