CODEWITHSUNDEEP
postgresql
user3007361
user3007361

Reputation: 63

Extracting a substring of a period-separated string

I need to get sub string of data float value which looks like

eg :32.85.67
   sometimes 32.4.67

I need to remove value from second dot in both occurrence(example).

Result:32.85 
         32.4

Thanks in advance

Upvotes: 0

Views: 402

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324531

If it has multiple periods it isn't a float at all, it's just a string. Use regular string processing facilities - pattern matching / regular expressions.

eg:

SELECT (regexp_matches('12.34.56', '\d+\.(\d+)\.\d+'))[1];

or:

SELECT substring('12.34.56' from '[0-9]+\.([0-9]+)\.[0-9]+');

The latter is probably a bit cleaner as it avoids the 1-element array unpack, and it's more standard.

Upvotes: 2

Related Questions