INSTR with an Occurrence that needs to be a variable

Transact-SQL
10

Hi harishgg1, Thank you so much for your answer. I'm sorry to say I don't have permission to write functions in the source :persevere:

After Wim_Leys suggested posting my problem elsewhere, I posted it on "stackoverflow", and the good news is that I have had some possible solutions put forward......

SELECT
  contact_data, 
  rtrim( 
    ltrim( 
      lower( contact_data ), 
      'abcdefghijlmnopqrstuvwxyz( )' 
    ), 
    ')'
  )
from pub.contacts

Although this could trip me up if there are additional non-ID numbers in the string.

The other alternative is to guess at how many possible number of instances of brackets there could be - but there shouldn't be a lot to be honest......

select 
  contact_data, 
  rtrim(
    substring (
      contact_data,
      greatest( 
        instr( contact_data, '(', 1, 1 ), 
        instr( contact_data, '(', 1, 2 ), 
        instr( contact_data, '(', 1, 3 ) 
      ) + 1
    ), 
    ')'
  )
from pub.contacts

I think I'm going to use the last solution utilising GREATEST and maybe add in a few more instances just to make sure I'm covered!

Thank you so much for your time on this, I really appreciate it.