英文:
how to remove one leading and trailing single quote(') from a string in Postgres
问题
I need some help removing 1 leading and 1 trailing single quote from my string in Postgres 14.
string is
'''alter table test add column col2 int[] default ''''{}'''' not null'''
desired output:
''alter table test add column col2 int[] default ''''{}'''' not null''
I've tried trim but it removes the quotes from around the curly brackets as well.
=# select trim('''alter table test add column col2 int[] default ''''{}'''' not null''');
btrim
------------------------------------------------------------------
'alter table test add column col2 int[] default ''{}'' not null'
英文:
I need some help removing 1 leading and 1 trailing single quote from my string in Postgres 14.
string is
'''alter table test add column col2 int[] default ''''{}'''' not null'''
desired output:
''alter table test add column col2 int[] default ''''{}'''' not null''
I've tried trim but it removes the quotes from around the curly brackets as well.
=# select trim('''alter table test add column col2 int[] default ''''{}'''' not null''');
btrim
------------------------------------------------------------------
'alter table test add column col2 int[] default ''{}'' not null'
答案1
得分: 2
可以使用正则表达式替换方法：
SELECT REGEXP_REPLACE(val, '^''|''$', '', 'g') AS output
FROM yourTable;
注意：
- 在 SQL 字符串中，一个单引号的字面表示是两个单引号。
- 正则表达式模式
^''|''$匹配字符串开头或结尾的单引号。 - 我们使用第四个参数
g进行全局替换。
英文:
We can use a regex replacement here:
<!-- language: sql -->
SELECT REGEXP_REPLACE(val, '^''|''$', '', 'g') AS output
FROM yourTable;
Notes:
- A literal single quote in a SQL string is represented by two single quotes.
- The regex pattern
^''|''$matches a single quote at the start or end of the string. - We use the
g4th parameter to do a global replacement.
答案2
得分: 0
SELECT TRIM('' FROM your_string) FROM your_table_name;
请注意有四个单引号。通过在两个单引号内部放置一个单引号来转义它。
英文:
SELECT TRIM('''' FROM your_string) FROM your_table_name;
Note that there are four single quotes. By putting two single quotes inside, one escape the other.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论