CODEWITHSUNDEEP
sqlpostgresql
ra_pri
ra_pri

Reputation: 175

I want to extract a string before and after a specific occurence of a specific character in sql

I have a column which is having data like this :

/hey[my]/file/exec[jhwwdfbfjuneinfcvdkvnkdvn]

I want to write a sql query to fetch only "file" from this column. I want to trim the string after the 3rd occurence of '/' character and before the 2nd occurence of '/' character. File string is dynamic, for every record the file name is different so can't use like.

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('/', col))) + 1, LEN(col) - LEN(LEFT(col, CHARINDEX ('/', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('/', col))) - 1);

Result - LINE 1: SELECT SUBSTRING(s.value, LEN(LEFT(s.value, charindex ('/', ... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

datatype of s.value is text. I am applying this query in postgresql database.

I don't know how to give the occurences in sql query.

Upvotes: 2

Views: 7196

Answers (1)

Kishan Bheemajiyani
Kishan Bheemajiyani

Reputation: 3439

You could do one thing split that string into array by split string and take interval that you want in you case it would be 3rd interval i guess.

So here is the simple example 

SELECt split_part(FirstName, '/', 3)) As firstindex,firstname from Persons_details;

Try this syntax and let me know if that is what you want.

Upvotes: 2

Related Questions