CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 1

How to return a substring from a text column

I have a table as follows, I would like to return name of the person only, example Will,Armstrong, from the 2nd column. How can I achieve this?

1. Will,Armstrong:similarity=99.8   2011-08-25 17:16:16
2. Tom,White:similarity=90.0        2011-08-24 16:25:15
3. Johnny,Thomas:similarity=80.0    2011-08-24 13:20:15
4. John,Broderick:similarity=70.0   2011-08-21 10:25:15

Thanks.

John

Upvotes: 0

Views: 85

Answers (3)

Johannes Weiss
Johannes Weiss

Reputation: 54011

If you want 'anything up to colon (:)' you could use:

SELECT SUBSTRING(column2 FROM '^[^:]+') FROM table;

or

SELECT SUBSTRING(column2 FROM 0 FOR POSITION(':' IN column2)) FROM table;

See here for string functions in PostgreSQL.

Upvotes: 0

Bohemian
Bohemian

Reputation: 424973

select split_part(col2, ':', 1) as name
from mytable

Upvotes: 0

Jacob
Jacob

Reputation: 43209

SELECT split_part(textcolumn, ":",1) FROM yourTable

You can use the one-based split_part function. You should also consider normalizing your database by saving the name and the similarity in different columns.

Upvotes: 2

Related Questions