title: I want to extract a string before and after a specific occurence of a specific character in sql
body: <p>I have a column which is having data like this :</p>
  
  <pre><code>/hey[my]/file/exec[jhwwdfbfjuneinfcvdkvnkdvn]
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('/', col))) + 1, LEN(col) - LEN(LEFT(col, CHARINDEX ('/', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('/', col))) - 1);
  </code></pre>
  
  <p>Result - <code>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.
  </code></p>
  
  <p>datatype of s.value is text.
  I am applying this query in postgresql database.</p>
  
  <p>I don't know how to give the occurences in sql query.</p>