String manipulation with Replace in SQL

Question

I am using a replace function to add some quotes around a couple of keywords. However, this replacement doesn't work for a few cases like the one below.

See example below.

This is the query:

replace(replace(aa.SourceQuery,'sequence','"sequence"'),'timestamp','"timestamp"')

Before:

select timestamp, SparkTimeStamp
from SparkRecordCounts

After:

select "timestamp", Spark"timestamp"
from SparkRecordCounts

However, I want it to be like:

select "timestamp", Sparktimestamp
from SparkRecordCounts

What is your RDBMS? Does it support regular expressions in replace functionality? — PM 77-1, Jul 27 '16 at 20:33
Oracle has a regexp_replace that will do this, but we really need to know your RDBMS. — Brandon Horsley, Jul 27 '16 at 20:50
Can you confirm what database you are using and, are you looking to quote reserved keywords that are used as identifiers in your SQL query strings? I assume this because obviously `timestamp` is a data type and `sequence` is a numeric sequence generator. — Jonathon Ogden, Jul 27 '16 at 20:59

score 0 · Answer 1 · edited May 23 '17 at 11:58

EDIT I wrote this before knowing what RDBMS you were using but have left it in case it helps someone else.

I think you are looking for word boundaries in your replacement, which are generally a job for regular expressions.

Oracle has one built in, called regexp_replace, and you could use something like this:

regexp_replace(aa.SourceQuery, '(^|\s|\W)timestamp($|\s|\W)', '\1"timestamp"\2')

The regular expression looks at the start for:

^ - the start of the line OR
\s - a space character OR
\W - a non-word character

It then matches timestamp, and must end with:

$ - the end of the line OR
\s - a space character OR
\W - a non-word character

Then, and only then, does it perform the replace. \1 and \2 are used to preserve what word boundary matched at the beginning and ending of the word.

I'm not sure how other databases handle regexp_replace, it looks like mysql can via a plugin like this but there may not be a native method.

SQL Server has a solution to something similar here

String manipulation with Replace in SQL

1 Answers1