Delete left two characters of string if A-Z or a-z

It's look to me that you'll need always the last eleven characters, right?
If so, something like this will work:

SELECT RIGHT(vatNumber,11) FROM vatTable

Select allOpen in new window

case when vat like '[a-Z][a-Z]%' then right(vat,len(vat) - 2)
     else vat
end

@awking00:
I think you need to add the case that there's only a letter instead of 2.

Yes, there might be 1, 2 or 3 letters as country-prefix.

@Delphiwizard: My observation for to consider only the last 11 characters is correct?

this also works...

 right('000' + vat ,11) vat_fixed

This works perfectly, thank you very much.

Sorry I gave the points by mistake to the incorrect answer.
I'll asked EE to reset.

Are you saying there could be 1, 2, or 3 characters in the prefix (or 0 if not prefix), but they would always be followed by 11 characters without the prefix?

I see now why I gave the points to the incorrect answer. Previously the assign point link were placed below the answer and now above :-)
Thank you all for your help.

PS:
@Hainkurt: Your second solution doesn't work as my data doesn't have the same length. Sometimes the whole string consists only out of 8 characters, other times it can be as much as 14 characters.

actually i never understood this stuff & patindex thing :) just tried something and it worked... then I checked details to see whats happening here:

stuff(vat, 1, patindex('%[0-9]%', vat)-1, '')

patindex('%[0-9]%', vat) > gives you index of first numeric character
patindex('%[0-9]%', vat)-1 > gives you index of last non-numeric (from start), or index of char before first numeric character
stuff(vat, 1, patindex('%[0-9]%', vat)-1, '') > removes from 1st character until last non-numeric character, or replaces it with ''

result is what you want :)

And that is a fact!
Thanks for the explanation.