I have run into an issue and have searched far and wide without much success. I have a SQL statement that pulls account numbers. The account numbers consist of four parts, but I need to separate one of the middle parts into its own column. I've seen various TRIM methods, but nothing seems to be working.
Essentially the account number comes out as:
1 02 345678901
Basically the data has 19 characters. There is a number (the first 1), four spaces, then two numbers (in this example the 02), several more spaces, and then the rest of the account number. What I am trying to pull out is just the 02 in the middle there. But, the kicker is that that number may not always be a 02. It can be 01, 02, 03... all the way to 29 I think is what we have.
I've tried using LEFT(ColumnName, 7) but that gives me the whole beginning. I've tried other variations but I feel like I am missing something obvious. Whatever the case, I just need the two numbers in the middle in their own column.
Any help or suggestions would be epically appreciated. Thanks! :D
If they always have this format, then use substring():
select substring(columnname, 6, 2)