How to pull integers from the middle of SQL query results

Question

I have a SQL table returning results as follows:

XY_4_**505543**_1227_1499547620

I am attempting to pull the bolded only. I have tried a mix of replace('XY_4_','') and len() with a -16. But I think I am approaching this wrong.

I was hoping to use something like the following, but replace doesn't allow wildcards:

select replace(replace(column, 'XY_4_','')'_%%%%_%%%%%%%%%%','') from table

Answer 1

Since your desired output string always begins from 6th character and contains 6 characters in total, you could use the SUBSTRING function to get the desired result.

SELECT SUBSTRING(column, 6, 6) FROM table;

Where the first parameter is the column name, the second is the start index (1 based) and the last parameter is the number of characters to retrieve.