How do I replace a string in SQL and make sure it has no extra spaces?

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

view raw SQL_replace_trim.sql hosted with ❤ by GitHub

 

Wanna check out how these come together in a real-life example?