CODEWITHSUNDEEP
postgresql
Kul
Kul

Reputation: 73

Querying part of a string

I am trying to query a column which contains string such as 

595.1,N30.10
630.5,E10

I have tried separating the two values into different columns 

split_part(code, ',', 1) AS code1, 
split_part(code, ',', 2) AS code2

But now I see that some of the rows have 3 (or could be more)

785.59, R57.1, R

I wonder if there is a way to specify and query only the first part of the string without having to split the string. In this case only look for enteries with 595.1,785.59 and ignore the rest. 

SELECT distinct ON (id) id,time,year,code
FROM data
where code= ANY('{595.1,785.59}');

Upvotes: 0

Views: 119

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

I think you already the have logic you need, you only need to thread it together:

SELECT DISTINCT ON (id) id, time, year, code
FROM data
WHERE split_part(code, ',', 1) = ANY('{595.1,785.59}');

This logic appears to be working in the demo below.

Demo

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

To query on first part of code column you can do like below 

SELECT * FROM tableName
WHERE split_part(code, ',', 1) = somevalue

Upvotes: 1

Related Questions