How to remove first & last character of a text?

Question

I have a record that was converted into text like this:

("{""ACC_CODE"":""0/000"",""ACC_DECIMAL"":2}"})

I want to remove the ( and the ) so that I could convert the text into json. How do I do that?

Edit: I don't want to use trim function because there are ( & ) characters in the original text.

I just want to remove the first & last character.

Solution 1

You can do:

select substr(col, 2, length(col) - 2)

Solution 2

t=# select rtrim(ltrim('({()})','('),')');
 rtrim
-------
 {()}
(1 row)

ltrim and rtim don't touch brackets inside, like trim itsel:

t=# select trim('({()})','()');
 btrim
-------
 {()}
(1 row)

Solution 3

Function regexp_replace() works for me.

E.g. to remove last 4 chars:

select 
regexp_replace('300PRIZE28NOV20183333\%%3BS\.com', '....$', '') campaign_name;

------------------------------
 300PRIZE28NOV20183333\%%3BS\

(1 row)

-- more general, remove last 8 chars.

select 
regexp_replace('300PRIZE28NOV20183333\%%3BS\.com', '.{8}$', '') campaign_name;
      campaign_name       
--------------------------
 300PRIZE28NOV20183333\%%

(1 row)

Solution 4

To Remove first Character from a string

select substr(col, 2)

To Remove Last Character from a string

select substr(col,1, length(col)-1)
select reverse(substr(reverse(col),2)

View more solutions

Comments

[Charlie 木匠]

if col is an expression, there will be some redundant calculation. e.g.: substring(substring(test_column, 'abc\.abcd\.id%3D.+?\%3BS\.com') from 30). Is there a way to avoid calling length() ?

[Charlie 木匠]

If rtrim() can specify length, it'll be perfect.

[Aaron]

for ms-sql use SUBSTRING