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
| -----------------------------------------------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?
