CODEWITHSUNDEEP
sqlregexpostgresql
Uday
Uday

Reputation: 161

Obtain substring in SQL

I have to obtain the substring in a string without giving position using SQL in PostgreSQL.

I have tried in this way but which is displaying only last number '2'

This way i have worked The String is P1.B1.12

SELECT SUBSTRING(REGISTRATIONNO from '.(.)*') AS REGISTRATIONNO 
FROM SUBSCRIBER;

The expected result is 12 - everything after the second .

Upvotes: 0

Views: 129

Answers (3)

Sajuna Fernando
Sajuna Fernando

Reputation: 1384

Maybe you can try this:

SELECT SUBSTRING_INDEX(REGISTRATIONNO, '.', -1) AS REGISTRATIONNO 
FROM SUBSCRIBER;

Upvotes: 0

Lajos Veres
Lajos Veres

Reputation: 13725

Can you try this?

SELECT SUBSTRING(REGISTRATIONNO from '\w*.\w*.(\d+)') AS REGISTRATIONNO 
FROM SUBSCRIBER;

Upvotes: 1

user330315
user330315

Reputation: 

select (regexp_split_to_array(registrationno, '\.'))[3]
from subscriber

Note that this assumes that the desired value is always at the third position. If this is not the case, the expression will return the wrong value.

Here is an SQLFiddle example: http://sqlfiddle.com/#!12/d41d8/1924

Upvotes: 1

Related Questions