Trying to capture server names from string.

A server name can be

• letters + digits
• letters + digits + letters (but not ‘root’)

Problem is that in circumstances the word ‘root’ gets added to the end of the string by the data source.

ab-vol-bapp000123-use-dev
ab-vol-bapp000123sql-use-dev

ab-vol-bapp000123root-use-dev
ab-vol-bapp000123sqlroot-use-dev

In the above cases, I need to get either

• app000123

Or

• app000123sql

However, struggling to capture the chrs after the digits whilst ignoring/excluding ‘root’

This is my best attempt:

(^ab-vol-)   # literal
([a-z]{2,4}) # 2-4 alphas
([0-9]{4,6}) # 4-6 numerics
(
  (?!root)   # ignore 'root'
  [a-z]{0,4} # 0-4 alphas
)?

Obviously my “ignore ‘root'” is not doing as described (last test line below fails), and I can see why – I just don’t know what the alternative answer is 😭

Appreciate any guidance!
Thanks

(Notes :Working in AWS redshift)