My query is the following:
SELECT id, category FROM table1
This returns the following rows:
ID|category
1 |{IN, SP}
2 |
3 |{VO}
Does anyone know how i can remove the first char and last char of the string in PostgreSQL, so it removes: {}?
-
Can these two characters show up somewhere else? (And supposed to be kept?)jarlh– jarlh2017-01-04 10:13:28 +00:00Commented Jan 4, 2017 at 10:13
-
No. I want to retrieve the String in my select statement without {} but it's a foreign column containing an arrayMarkie Doe– Markie Doe2017-01-04 10:15:06 +00:00Commented Jan 4, 2017 at 10:15
Add a comment
|
7 Answers
Not sure, what you mean with "foreign column", but as the column is an array, the best way to deal with that is to use array_to_string()
SELECT id, array_to_string(category, ',') as category
FROM table1;
The curly braces are not part of the stored value. This is just the string representation of an array that is used to display it.
Comments
Either using multiple REPLACE functions.
SELECT id, REPLACE(REPLACE(category, '{', ''), '}', '')
FROM table1
Or using a combination of the SUBSTRING, LEFT & LENGTH functions
SELECT id, LEFT(SUBSTRING(category, 2, 999),LENGTH(SUBSTRING(category, 2, 999)) - 1)
FROM table1
Or just SUBSTRING and LENGTH
SELECT id, SUBSTRING(category, 2, LENGTH(category)-2)
FROM table1
1 Comment
pozs
len() is not a PostgreSQL function (neither is an ANSI SQL one). You could use length(), char_length() or character_length()You could replace the {} with an empty string
SELECT id, replace(replace(category, '{', ''), '}', '') FROM table1
Comments
select id,
substring(category,charindex('{',category,len(category))+2,len(category)-2)
from table1;
select id
,left(right(category,length(category)-1),length(category)-2) category
from boo
select id
,trim(both '{}' from category)
from boo
Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string
Comments
The syntax for the replace function in PostgreSQL is:
replace( string, from_substring, to_substring )
Parameters or Arguments
string
The source string.
from_substring
The substring to find. All occurrences of from_substring found within string are replaced with to_substring.
to_substring
The replacement substring. All occurrences of from_substring found within string are replaced with to_substring.
Comments
UPDATE dbo.table1
SET category = REPLACE(category, '{', '')
WHERE ID <=3
UPDATE dbo.table1
SET category = REPLACE(category, '}', '')
WHERE ID <=3
