CODEWITHSUNDEEP
sqlpostgresql
Markie Doe
Markie Doe

Reputation: 125

Use of substring in SQL

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: {}?

Upvotes: 2

Views: 472

Answers (7)

Muddassir Irahad
Muddassir Irahad

Reputation: 41

UPDATE dbo.table1
SET category = REPLACE(category, '{', '')
WHERE ID <=3

UPDATE dbo.table1
SET category = REPLACE(category, '}', '')
WHERE ID <=3

Upvotes: -1

Vivek S.
Vivek S.

Reputation: 21905

select id
      ,left(right(category,length(category)-1),length(category)-2) category 
from boo

select id
       ,trim(both '{}' from category) 
from boo

Trim():

Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string

Upvotes: 0

anjali
anjali

Reputation: 84

select id,
      substring(category,charindex('{',category,len(category))+2,len(category)-2) 
from table1;

Upvotes: 0

Matt
Matt

Reputation: 15071

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

Upvotes: 2

user330315
user330315

Reputation:

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.

Upvotes: 5

Queen
Queen

Reputation: 21

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.

Upvotes: -1

juergen d
juergen d

Reputation: 204766

You could replace the {} with an empty string

SELECT id, replace(replace(category, '{', ''), '}', '') FROM table1

Upvotes: 1

Related Questions